VBA - hide rows based on multiple cell values in different row

[Connorwild]

Hi all,

I have limited knowledge of VBA - mainly pieced together from various google searches until it works. From this, I've been able to create macro buttons to reset my data and hide/unhide rows based on single criteria, but my problem comes when trying to hide/unhide rows (12:27) based on numerical criteria pulled from two cells in a different row (6).

My current code:

Private Sub Worksheet_Change_B(ByVal Target As Range)

    Select Case Range("D6").Value

        Case "": Range("12:27").EntireRow.Hidden = True

        Case Is < 100000: Range("12:27").EntireRow.Hidden = True

        Case Is >= 100000: Range("12:14").EntireRow.Hidden = False

    End Select

End Sub

Currently, if D6 is empty or less than 100,000, the range is hidden. More than or equal to 100,000, the range is shown. Ideally I'd like to include the value from C6 in the calculation, this will be empty or more than 0 - this is where I struggle and cannot decipher what I'm finding online.

If C6 is more than 1 AND D6 is more than 100,000 - show range. All other scenarios, including a partial combination (i.e. C6 not empty but D6 less than 100,000), the range would remain hidden.

Whilst I know the below isn't correct, it might demonstrate what I mean a bit clearer:

C6 or D6 "" or D6 < 100000: Range("12:27").EntireRow.Hidden = True
C6 >1 and D6 >= 100000: Range("12:27").EntireRow.Hidden = False


Thanks in advance!
Connor

 

[Jeffrey Mahoney]

I had to decipher your contrasting statements. The statement is what I used.
"If C6 is more than 1 AND D6 is more than 100,000 - show range. All other scenarios, including a partial combination (i.e. C6 not empty but D6 less than 100,000), the range would remain hidden. "

Dim C As Variant
  Dim D As Variant
  C = Range("C6").Value
  D = Range("D6").Value
    
  If C > 1 And D >= 100000 Then
    Range("12:27").EntireRow.Hidden = False
  Else
    Range("12:27").EntireRow.Hidden = True
  End If

 

[Connorwild]

Hi Jeffrey,

Thanks for your reply and sorry for any conflicting statements. Unfortunately my limited knowledge of VBA means I cant get this to work within the existing workbook. I have 6 workbook change statements which form the basis of the hide/unhide structure of the sheet as below, as well as 2 buttons to reset the data. The statement you provided is to replace Change_B - how do i fit this in or around the current statements?

Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheet_Change_A Target
    Worksheet_Change_B Target
    Worksheet_Change_C Target
    Worksheet_Change_D Target
    Worksheet_Change_E Target
    Worksheet_Change_F Target
  End Sub

Private Sub Worksheet_Change_A(ByVal Target As Range)
    Select Case Range("A6").Value
        Case "": Range("7:8").EntireRow.Hidden = True
        Case Is > 750000: Range("7:8").EntireRow.Hidden = False
        Case Is <= 750000: Range("7:8").EntireRow.Hidden = True
    End Select
End Sub

Private Sub Worksheet_Change_B(ByVal Target As Range)
    Select Case Range("D6").Value
        Case "": Range("12:27").EntireRow.Hidden = True
        Case Is < 100000: Range("12:27").EntireRow.Hidden = True
        Case Is >= 100000: Range("12:14").EntireRow.Hidden = False
    End Select
End Sub


Private Sub Worksheet_Change_C(ByVal Target As Range)
    Select Case Range("D13").Value
        Case "Please Select From Dropdown": Range("14:27").EntireRow.Hidden = True
        Case "Yes": Range("14:27").EntireRow.Hidden = True
        Case "No": Rows(14).EntireRow.Hidden = False
    End Select
End Sub

Private Sub Worksheet_Change_D(ByVal Target As Range)
    Select Case Range("D14").Value
        Case "Please Select From Dropdown": Range("15:27").EntireRow.Hidden = True
        Case "Yes": Range("15:27").EntireRow.Hidden = False
        Case "No": Range("15:27").EntireRow.Hidden = True
    End Select
End Sub

Private Sub Worksheet_Change_E(ByVal Target As Range)
    Select Case Range("E10").Value
        Case "Hide Maximum": Range("28:30").EntireRow.Hidden = True
        Case "Show Maximum": Range("28:30").EntireRow.Hidden = False
    End Select
End Sub

Private Sub Worksheet_Change_F(ByVal Target As Range)
     If Target.Address = "$D$13" Then
     Range("D14").Value = "Please Select From Dropdown"
    End If
End Sub

Sub Clearcells()
Range("D13", "D14").Value = "Please Select From Dropdown"
Range("E10").Value = "Hide Maximum"
Range("A6:D6").Value = ""
Range("A11:D11").Value = ""
End Sub

Sub Reset_Dropdowns()
    Range("D13:D14").Value = "Please Select From Dropdown"
    Range("E10").Value = "Hide Maximum"
End Sub

 

[Jeffrey Mahoney]

How is this for an efficient solution?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i As Range
  Dim u As Range
  Dim Cel As Range
  
  Set u = Range(Range("D6"), Range("D13"), Range("D14"), Range("E10"))
  
  Set i = Intersect(u, Target)
  If i Is Nothing Then Exit Sub
  
  For Each Cel In i
    Select Case Cel.Address(0, 0)
      Case "D6"
        Select Case Range("D6").Value
          Case ""
            Range("12:27").EntireRow.Hidden = True
          Case Is < 100000
            Range("12:27").EntireRow.Hidden = True
          Case Is >= 100000
            Range("12:14").EntireRow.Hidden = False
        End Select
      
      Case "D13"
        Select Case Range("D13").Value
          Case "Please Select From Dropdown"
            Range("14:27").EntireRow.Hidden = True
          Case "Yes"
            Range("14:27").EntireRow.Hidden = True
          Case "No"
            Rows(14).EntireRow.Hidden = False
        End Select
        
      Case "D14"
        Select Case Range("D14").Value
          Case "Please Select From Dropdown"
            Range("15:27").EntireRow.Hidden = True
          Case "Yes"
            Range("15:27").EntireRow.Hidden = False
          Case "No"
            Range("15:27").EntireRow.Hidden = True
        End Select
        
      Case "E10"
        Select Case Range("E10").Value
          Case "Hide Maximum"
            Range("28:30").EntireRow.Hidden = True
          Case "Show Maximum"
            Range("28:30").EntireRow.Hidden = False
      End Select
      
    End Select
  
  Next Cel
End Sub

 

[Connorwild]

Hi Jeffrey,

There's an issue with the PrivateSub & Set U lines; I get an error stating "Wrong number of arguments or invalid property assignment". This highlights the first 'Range' in the Set u = line, then highlights the whole PrivateSub line in yellow.

I'm extremely grateful for your help, but this is so far beyond my limited knowledge that I don't know the cause let alone how to rectify! Also, forgive my ignorance but there is no reference to C6 being >0 in the first statement, does this need to be added? D6 would still follow the same rules, but only if C6 >0 as well.

Thank you!

 

[Jeffrey Mahoney]

Sorry about that. Change that to
Set u = Union(Range("D6"), Range("D13"), Range("D14"), Range("E10"))

 

[Connorwild]

Thanks again Jeffrey!

This seems to work as intended now, however I now have two further questions.

The first relates to auto updating; my cobbled together VBA ran every time a change was detected, so rows would show/hide immediately. Is there any way to make this automatic? I had thought it was the line "Private Sub Worksheet_Change (ByVal Target As Range)" which instigated this but the code you've provided doesn't run until I click off the cell and back on.

The second relates to my original question of only showing rows based on the answers in two cells. You provided a statement as your initial answer - would I slot this in to the longer VBA or would it need it's own argument?

 

[Jeffrey Mahoney]

Hmmm, maybe I was in a hurry the first time. Yes, the SUB Private Sub Worksheet_Change (ByVal Target As Range) is the one. You could move all that code to that and it would work when any of the cells:
Range(Range("D6"), Range("D13"), Range("D14"), Range("E10"))
are changed.

 

[Connorwild]

Thats great thanks Jeffrey!