问Excel VBA隐藏行

EN

Option Explicit

Sub HideRows()

    Const StartRow As Long = 9
    Const EndRow As Long = 89
    Const ColNum As Long = 3
    Dim WorksheetNames As Variant
    WorksheetNames = Array("NAMES", "AUGUST") ' add more
    
    Dim ws As Worksheet ' Current Worksheet
    Dim rg As Range ' Current Range
    Dim hrg As Range ' Current Hide Range
    Dim cCell As Range ' Current Cell in Range
    
    ' Loop through the worksheets in the workbook containing this code.
    For Each ws In ThisWorkbook.Worksheets(WorksheetNames)
        ' Create a reference to the range of the current worksheet.
        Set rg = ws.Range(ws.Cells(StartRow, ColNum), ws.Cells(EndRow, ColNum))
        ' or using resize:
        'Set rg = ws.Cells(StartRow, ColNum).Resize(EndRow - StartRow + 1)
        ' Loop through the cells of the current range.
        For Each cCell In rg.Cells
            If IsEmpty(cCell) Then ' cell is empty
                ' Combine ('add') the current cell into the hide range.
                If Not hrg Is Nothing Then ' for all except the first
                    Set hrg = Union(hrg, cCell)
                Else ' for the first
                    Set hrg = cCell
                End If
            'Else ' cell is not empty - do nothing
            End If
        Next cCell
        ' Unhide all rows of the current range of the current worksheet.
        rg.EntireRow.Hidden = False
        If Not hrg Is Nothing Then ' there are combined cells
            ' Hide the rows of the hide range.
            hrg.EntireRow.Hidden = True
            ' Reset the hide range variable for the next worksheet.
            ' Also, note that 'Union' works only with ranges from one worksheet.
            Set hrg = Nothing
        'Else ' there are no combined cells - do nothing
        End If
    Next ws

End Sub

Sub HideRows()

    Dim ws As Worksheet
    For Each ws In Worksheets(Array("NAMES", "AUGUST"))
    
        StartRow = 9
        EndRow = 89
        ColNum = 3
    
        For i = StartRow To EndRow
            If Not IsEmpty(Cells(i, ColNum).Value) Then
                ws.Cells(i, ColNum).EntireRow.Hidden = False
            Else
                ws.Cells(i, ColNum).EntireRow.Hidden = True
            End If
        Next i
    
    Next
End Sub