I have a sheet (Sheet3) within a workbook that I would like to write VBA to hide multiple rows based on cell value in multiple ranges. The VBA would have to run through two different steps; the first would be if the first cell within the specified range is blank then hide the entire range (except range 1 since the first cell would never be blank). The second step would be if the first cell in range is not blank, then hide rows in that range that are blank. Here are the specifics:
Range 1
- A11:A60 - Hide rows that are blank in range
Range 2
- A71:A120 - If cell A71 is blank, Hide A71:A120. Otherwise hide all rows that are blank in range A71:A120.
Range 3
- A131:A180 - If cell A131 is blank, Hide A131:A180. Otherwise hide all rows that are blank in range A131:A180.
Range 4
- A191:A240 - If cell A191 is blank, Hide A191:A240. Otherwise hide all rows that are blank in range A191:A240.
Range 5
A251:A300 - If cell A251 is blank, Hide A251:A300. Otherwise hide all rows that are blank in range A251:A300.
Public Sub HideRowsSummary() Dim wsMySheet As Worksheet Dim lngMyRow As Long, unionRng As Range Application.ScreenUpdating = False For Each wsMySheet In ThisWorkbook.Sheets Select Case wsMySheet.Name Case Is = Sheet3 .Range("A11:A60", "A71:A120", "A131:A180", "A191:A240", "A251:A300").EntireRow.Hidden = False For lngMyRow = 11 To 60 If Len(.Range("A" & lngMyRow)) = 0 Then If Not unionRng Is Nothing Then Set unionRng = Union(unionRng, .Range("A" & lngMyRow)) Else Set unionRng = .Range("A" & lngMyRow) End If End If Next lngMyRow End With End Select If Not unionRng Is Nothing Then unionRng.EntireRow.Hidden = True Set unionRng = Nothing Next wsMySheet Application.ScreenUpdating = True
End Sub
In your question
#2, 3, 4, 5
all follow similar logic.The first
i
loop tackles#1
. The next portion of the code tackles#2
. You can simply copy/paste the bottom portion and change your test values to complete your ask.This can be improved by
A) Use
For Each
loop instead ofFor i
loop B) Instead of hiding rows one by one, add them to a collection of rows as a (Union
) and hide theUnion
all at once