Hide multiple rows based on multiple ranges cell v

问题:

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

1. A11:A60 - Hide rows that are blank in range

Range 2

2. A71:A120 - If cell A71 is blank, Hide A71:A120. Otherwise hide all rows that are blank in range A71:A120.

Range 3

3. A131:A180 - If cell A131 is blank, Hide A131:A180. Otherwise hide all rows that are blank in range A131:A180.

Range 4

4. A191:A240 - If cell A191 is blank, Hide A191:A240. Otherwise hide all rows that are blank in range A191:A240.

Range 5

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

回答1:

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.

---

Option Explicit

Sub HideMe()

Dim i As Integer

With ThisWorkbook.Sheets("Sheet3")
    For i = 11 To 60
        .Range("A" & i).EntireRow.Hidden = .Range("A" & i) = vbNullString
    Next i

    'Repeat this portion for you other ranges that follow the same rules
    If .Range("A" & 71) = vbNullString Then
        .Range("A71:A120").EntireRow.Hidden = True
    Else
        For i = 72 To 120
            .Range("A" & i).EntireRow.Hidden = .Range("A" & i) = vbNullString
        Next i
    End If
End With

End Sub

---

This can be improved by

A) Use For Each loop instead of For i loop B) Instead of hiding rows one by one, add them to a collection of rows as a (Union) and hide the Union all at once