Excel VBA隐藏行

Question

在下图中，我试图隐藏某些单元格中的空行(例如第 39 行到第 48 行)。是否可以一键完成？我打算用VBA来做。

这是我目前使用的公式，但问题是我想要隐藏的单元格可能不是从第 39 行开始或在第 48 行结束，这取决于数据。

Sub HideRows()
    Dim ws As Worksheet
    For Each ws In Worksheets(Array("NAMES", "AUGUST"))
        'ws.Rows("39:48").Hidden = True
Next
End Sub

Answer 1

隐藏“空”行

这是一种稍微不同的方法:

使用 Option Explicit

使用常量和变量

使用 For Each...Next工作表和单元格的循环

限定所有对象(例如 ws.Cells or rg.Cells ，而不仅仅是 Cells )

将空单元格组合成一个范围

一次性取消隐藏所有行，然后在另一个中隐藏“空”行 (go)

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