Adding another range to VBA to hide rows

[Stuepef]

I have written VBA code to hide specific ranges in 4 different tabs in my workbook. I am looking to add another range to the code that would hide rows based on cell value in the same range on each of the 4 different tabs. If the cell in range B9-B13 is blank, then hide that specific row on each tab.

Here is my current code:

[TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]Sub  HideRowsSummary()[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]     Dim wsMySheet As Worksheet[/TD]
[/TR]
[TR]
[TD]     Dim lngMyRow  As Long[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]     Application.ScreenUpdating = False[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]     For Each wsMySheet In ThisWorkbook.Sheets[/TD]
[/TR]
[TR]
[TD]     Select Case wsMySheet.Name[/TD]
[/TR]
[TR]
[TD]        Case Is = "Summary 1", "Summary (2)",  "Summary (3)", "Summary (4)"[/TD]
[/TR]
[TR]
[TD]                For lngMyRow = 73 To 24 Step  -1 'Need to work backwards through the rows when hiding or deleting[/TD]
[/TR]
[TR]
[TD]                    If  Len(wsMySheet.Range("A" & lngMyRow)) = 0 Then[/TD]
[/TR]
[TR]
[TD]                         wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = True[/TD]
[/TR]
[TR]
[TD]                    Else[/TD]
[/TR]
[TR]
[TD]                         wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = False[/TD]
[/TR]
[TR]
[TD]                    End If[/TD]
[/TR]
[TR]
[TD]                Next lngMyRow[/TD]
[/TR]
[TR]
[TD]         End Select[/TD]
[/TR]
[TR]
[TD]     Next wsMySheet[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]     Application.ScreenUpdating = True[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for the help!

 

[JackDanIce]

Do you mean hide rows if A24:A73 in sheets "Summary *" is blank AND hide rows in range B9:B13 of same sheet if blank?

 

[Stuepef]

Yes that is correct, I only want to add the hiding of range B9:B13 part if blank on the same Summary tabs.

 

[JackDanIce]

Untested, try:

Sub HideRows()


    Dim x   As Long
    
    Application.ScreenUpdating = False
    
    For x = 1 To Worksheets.Count
        With sheets(x)
            If InStr(.Name, "Summary") > 0 Then
                With .Cells(24, 1).Resize(50)
                    .EntireRow.Hidden = False
                    .SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
                End With
                .Cells(9, 2).Resize(5).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
            End If
        End With
    Next x
    
    Application.ScreenUpdating = True
                
End Sub

 

[Stuepef]

I receive a run error 1004 - no cells found on this line:

[COLOR=#333333] .SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True[/COLOR]

 

[JackDanIce]

This suggests the sheet where this error occured has no blank cells in range A24:A73.
You can wrap that line with:

On Error Resume Next
.SpecialCells(xlCellTypeBlanks).entireRow.Hidden = True
On Error Goto 0

Which will prevent the error if no cells are blank and macro continue to next sheet.