Excel VBA to hide rows based on cell value across multiple sheets

[Stuepef]

I am attempting to write excel VBA to hide blank rows across multiple worksheets based on cell value in Column A. Sheet 1 is where I use formulas to populate the rest of the sheets so this sheet will need to be excluded from the VBA. Sheet 2-5 are identical in terms of last names displayed and the exact same range/# of empty rows. Sheets 6-8 will look identical to sheets 2-5, but have a different range that will need blank rows hidden.

Sheet 2-5 Range A9-A58:
Column A
Ross
Kirby
George
Stevens
Sears
(blank)
(blank)
(Blank)

Sheet 6-8 Range A16-A65:
Column A
Ross
Kirby
George
Stevens
Sears
(blank)
(blank)
(Blank)

My current VBA code:

Sheets(array("Sheet 2", "Sheet 3", "Sheet 4", Sheet 5"))
Dim aStart as Long,
Dim aStop as Long,
aStart = 19
aStop = 58
Dim i as Long,
For i = aStart to aStop
If Cells( i, "A").Value = "" Then
Rows(i)EntireRow.Hidden = True
End If
Next i
End Sub

The code works only for the active sheet I run the code on and not the other sheets listed. Also, I am not sure where to add the code for Sheets 6-8 with the different range than sheets 2-5. Any help is greatly appreciated!

 

[Trebor76]

Hi Stuepef,

Welcome to MrExcel!!

This should do the job for you:

Option Explicit
Sub Macro1()

    Dim wsMySheet As Worksheet
    Dim lngMyRow  As Long
    
    Application.ScreenUpdating = False
    
    For Each wsMySheet In ThisWorkbook.Sheets
        Select Case wsMySheet.Name
            Case Is = "Sheet2", "Sheet3", "Sheet4", "Sheet5"
                For lngMyRow = 58 To 9 Step -1 'Need to work backwards through the rows when hiding or deleting
                    If Len(wsMySheet.Range("A" & lngMyRow)) = 0 Then
                        wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = True
                    Else
                        wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = False
                    End If
                Next lngMyRow
            Case Is = "Sheet6", "Sheet7", "Sheet8"
                For lngMyRow = 65 To 16 Step -1 'Need to work backwards through the rows when hiding or deleting
                    If Len(wsMySheet.Range("A" & lngMyRow)) = 0 Then
                        wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = True
                    Else
                        wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = False
                    End If
                Next lngMyRow
        End Select
    Next wsMySheet
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Stuepef]

Hi Stuepef,

Welcome to MrExcel!!

This should do the job for you:

Option Explicit
Sub Macro1()

    Dim wsMySheet As Worksheet
    Dim lngMyRow  As Long
    
    Application.ScreenUpdating = False
    
    For Each wsMySheet In ThisWorkbook.Sheets
        Select Case wsMySheet.Name
            Case Is = "Sheet2", "Sheet3", "Sheet4", "Sheet5"
                For lngMyRow = 58 To 9 Step -1 'Need to work backwards through the rows when hiding or deleting
                    If Len(wsMySheet.Range("A" & lngMyRow)) = 0 Then
                        wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = True
                    Else
                        wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = False
                    End If
                Next lngMyRow
            Case Is = "Sheet6", "Sheet7", "Sheet8"
                For lngMyRow = 65 To 16 Step -1 'Need to work backwards through the rows when hiding or deleting
                    If Len(wsMySheet.Range("A" & lngMyRow)) = 0 Then
                        wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = True
                    Else
                        wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = False
                    End If
                Next lngMyRow
        End Select
    Next wsMySheet
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

Thank you for welcoming to me to MrExcel! This code works perfectly, thank you so much Robert!

 

[Stuepef]

Thank you so much, Robert! This code works like a charm! Is there a way to unhide the same ranges that were hidden in the code above with a separate VBA?

 

[Trebor76]

Thank you so much, Robert! This code works like a charm!

You're welcome.

Is there a way to unhide the same ranges that were hidden in the code above with a separate VBA?

The code will automatically unhide any row in Col. A that has a value but if there's no formula in these rows that may populate try this:

Option Explicit
Sub Macro2()

    Dim wsMySheet As Worksheet
    Dim lngMyRow  As Long
    
    Application.ScreenUpdating = False
    
    For Each wsMySheet In ThisWorkbook.Sheets
        Select Case wsMySheet.Name
            Case Is = "Sheet2", "Sheet3", "Sheet4", "Sheet5"
                For lngMyRow = 58 To 9 Step -1 'Need to work backwards through the rows when hiding or deleting
                    If Len(wsMySheet.Range("A" & lngMyRow)) = 0 Then
                        wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = False
                    End If
                Next lngMyRow
            Case Is = "Sheet6", "Sheet7", "Sheet8"
                For lngMyRow = 65 To 16 Step -1 'Need to work backwards through the rows when hiding or deleting
                    If Len(wsMySheet.Range("A" & lngMyRow)) = 0 Then
                        wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = False
                    End If
                Next lngMyRow
        End Select
    Next wsMySheet
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Stuepef]

Again, works like a charm! Thank you for your help!

 

[Trebor76]

No problem

 

[Trebor76]

Actually if you want to just unhide every row in the each range for the applicable tab, you can do this:

Option Explicit
Sub Macro2()

    Dim wsMySheet As Worksheet
    Dim lngMyRow  As Long
    
    Application.ScreenUpdating = False
    
    For Each wsMySheet In ThisWorkbook.Sheets
        Select Case wsMySheet.Name
            Case Is = "Sheet2", "Sheet3", "Sheet4", "Sheet5"
                wsMySheet.Range("A9:A58").EntireRow.Hidden = False
            Case Is = "Sheet6", "Sheet7", "Sheet8"
                wsMySheet.Range("A16:A65").EntireRow.Hidden = False
        End Select
    Next wsMySheet
    
    Application.ScreenUpdating = True

End Sub

This will be much faster as there's no looping (although your datasets are quiet small so you mightn't notice a difference).

Robert