Auto Hide/Unhide Rows based on Criteria and Range

[TracyNakamura]

I am trying to auto hide/unhide rows based on if a cell's formula is 0. However, if the cell is not 0, I want it to also go through a specific range and hide rows that are blank within that range.

I was able to find a code to make the first part work:

Sub HideRowsIfBlank()

Dim consultant As Range

Set consultant = Range("A6")

Select Case consultant

Case Is = 0: Rows("5:8").EntireRow.Hidden = True

Case Is <> 0: Rows("5:8").EntireRow.Hidden = False

End Select

Set consultant = Range("A10")

Select Case consultant

Case Is = 0: Rows("9:12").EntireRow.Hidden = True

Case Is <> 0: Rows("9:12").EntireRow.Hidden = False

End Select

Set consultant = Range("A14")

Select Case consultant

Case Is = 0: Rows("13:25").EntireRow.Hidden = True

Case Is <> 0: Rows("13:25").EntireRow.Hidden = False

End Select

End Sub

My issue is the second part. I have the data pulling over from another sheet and based on a given month, the number of items in the rows are different so dependent from the sheet I'm pulling from, there could be extra blank lines within that range. For it to print nicely, I want to get rid of the excess rows if they are blank. When I tried If/Then statements, it wouldn't work because of the formula in the cell - if the formula returned 0/null, technically the cell wasn't "blank."

For example on the 3rd set.

Set consultant = Range("A14")

Select Case consultant

Case Is = 0: Rows("13:25").EntireRow.Hidden = True

Case Is <> 0: Rows("13:25").EntireRow.Hidden = False

End Select

I want it to run and then if it's false, I want it to also run through a set range A14:A24 and hide the rows that are 0 based on column A. Each cell has a formula.

Is this something that's possible?

Thank you!

[#2]

@TracyNakamura 

Is possible to achieve this by modifying your existing code. You can use a loop to iterate through the specified range and hide or unhide rows based on your criteria.

Here is an example modification to include the second part of your requirement:

The VBA code is untested. Please back up your file in advance.

Sub HideRowsIfBlank()
    Dim consultant As Range
    Dim targetRange As Range
    Dim cell As Range
    
    ' Set the initial consultant range
    Set consultant = Range("A6")
    
    ' Loop through each consultant range
    Do Until consultant.Value = ""
        ' Determine the target range based on the consultant range
        Select Case consultant.Address
            Case "$A$6": Set targetRange = Rows("5:8")
            Case "$A$10": Set targetRange = Rows("9:12")
            Case "$A$14": Set targetRange = Rows("13:25")
            ' Add more cases as needed for other consultant ranges
        End Select
        
        ' Hide or unhide rows based on the consultant range value
        If consultant.Value = 0 Then
            targetRange.EntireRow.Hidden = True
        Else
            targetRange.EntireRow.Hidden = False
            
            ' Check for additional conditions (e.g., hide rows with 0 in a specific range)
            For Each cell In Range("A14:A24")
                If cell.Value = 0 Then
                    cell.EntireRow.Hidden = True
                Else
                    cell.EntireRow.Hidden = False
                End If
            Next cell
        End If
        
        ' Move to the next consultant range
        Set consultant = consultant.Offset(4, 0)
    Loop
End Sub

This modified code uses a loop to go through each consultant range, and within each range, it hides or unhides rows based on the consultant range value. Additionally, it checks a specific range (A14:A24 in this example) and hides rows with 0 in column A if the consultant range value is not 0. You can customize the code by adding more cases for other consultant ranges as needed.

The text, steps and vba code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

@NikolinoDE This is awesome! I feel like it's almost there. Is it possible to set different target ranges for the additional conditions? Or can I tell it to use the targetRange in the case? I tried editing it to "For Each Cell In targetRange" and "consultant.Range" and that did not work. 

' Check for additional conditions (e.g., hide rows with 0 in a specific range)
            For Each cell In Range("A14:A24")
                If cell.Value = 0 Then
                    cell.EntireRow.Hidden = True
                Else
                    cell.EntireRow.Hidden = False
                End If

Quick question about this one:

 ' Move to the next consultant range
        Set consultant = consultant.Offset(4, 0)

What is the #4 mean in this? Does it only go through 4 lines? 

Thank you!