Help with VBA code to hide rows with zero values

[onthegreen03]

Hello. Below is some code I wrote which hides/unhides certain rows when a selection is made from a drop down list. It runs great with no issues. My question is this. In the first grouping I am asking rows 16-43 to be unhidden along with rows 15 and 101 which are always to remain unhidden. Now I want to add another rule which states that in those unhidden rows (16-43), hide the rows that are showing a zero value in a particular column (in this case it is column BE). How do I add a second condition which hides any row in that range (rows 16-43) that have a zero value in column BE?

Sub ListSelection()

    If Range("K2").Value = "Precision Diagnostics" Then
        Rows("15").EntireRow.Hidden = False
        Rows("16:43").EntireRow.Hidden = False
        Rows("44:100").EntireRow.Hidden = True
        Rows("101").EntireRow.Hidden = False
   
    ElseIf Range("K2").Value = "IGT" Then
        Rows("15").EntireRow.Hidden = False
        Rows("45:47").EntireRow.Hidden = False
        Rows("16:44").EntireRow.Hidden = True
        Rows("48:100").EntireRow.Hidden = True
        Rows("101").EntireRow.Hidden = False

    ElseIf Range("K2").Value = "Connected Care" Then
        Rows("15").EntireRow.Hidden = False
        Rows("49:89").EntireRow.Hidden = False
        Rows("16:48").EntireRow.Hidden = True
        Rows("90:100").EntireRow.Hidden = True
        Rows("101").EntireRow.Hidden = False
       
    ElseIf Range("K2").Value = "Health Systems Equipment" Then
        Rows("15").EntireRow.Hidden = False
        Rows("91:93").EntireRow.Hidden = False
        Rows("16:90").EntireRow.Hidden = True
        Rows("94:100").EntireRow.Hidden = True
        Rows("101").EntireRow.Hidden = False
       
    ElseIf Range("K2").Value = "Health Systems" Then
        Rows("15").EntireRow.Hidden = False
        Rows("95:98").EntireRow.Hidden = False
        Rows("16:94").EntireRow.Hidden = True
        Rows("99:100").EntireRow.Hidden = True
        Rows("101").EntireRow.Hidden = False
       
    ElseIf Range("K2").Value = "Health Tech" Then
        Rows("15:101").EntireRow.Hidden = False

End If


End Sub

 

[Domenic]

Try replacing...

Rows("16:43").EntireRow.Hidden = False

with

Dim currentCell As Range
For Each currentCell In Range("BE16:BE43")
    If currentCell.Value = 0 Then
        currentCell.EntireRow.Hidden = True
    Else
        currentCell.EntireColumn.Hidden = False
    End If
Next currentCell

Or, simply...

Dim currentCell As Range
For Each currentCell In Range("BE16:BE43")
    currentCell.EntireRow.Hidden = (currentCell.Value = 0)
Next currentCell

Hope this helps!

 

[onthegreen03]

Awesome, thank you very much for your help!

 

[Domenic]

You're very welcome, cheers!