excel - 如果单元格范围内的所有可见单元格在 VBA for Excel 中均为空白，如何隐藏行？

Question

我在我的 Excel 电子表格中运行一些 VBA 宏，以帮助我从 View 中消除不相关的数据，然后允许我导出/复制我拥有的整个数据集的子集。我的电子表格大小是 Columns(A:BN) 和 Rows(1:4693)，我认为这是一个相当大的数据集。 Columns(A:G) 将始终保持可见。我只对隐藏 Columns(H:BN) 中的内容感兴趣。

首先，如果我想保留列，我将列值设置为“Y”，然后单击“隐藏列”按钮。我通过将此子例程附加到按钮来做到这一点:

Sub Hidecolumn()
Dim p As Range
    For Each p In Range("H1:BN1").Cells
        If p.Value = "N" Then
            p.EntireColumn.Hidden = True          
        End If
    Next p  
End Sub

这完美地工作。

我还有一个“显示所有列”按钮，让所有隐藏的列重新出现，我这样做:

Sub Unhidecolumn()
Dim p As Range
    For Each p In Range("H1:BN1").Cells
        If p.Value = "Y" Or p.Value = "N" Then
            p.EntireColumn.Hidden = False    
        End If
    Next p   
End Sub

现在我有了我想要的列，现在我需要隐藏所有不需要的行。如果存在行列关系，则每个单元格中都会有一个“Y”。如果没有关系，它将是空白的。我想隐藏单元格范围内所有可见单元格为空白的所有行。单元格区域中的所有单元格必须为空白。

隐藏所有不需要的行后，我可以复制剩余/可见数据并将其复制到新工作簿以与他人共享，因为他们只需要与他们相关的数据。

最后，就像我能够取消隐藏所有隐藏的列一样，我也需要能够取消隐藏所有隐藏的行。

最后，我想要我的工作表上的 4 个按钮。
1.隐藏列
2.取消隐藏列
3.隐藏行
4.取消隐藏行

我已经有了前 2 个，现在我需要最后 2 个。请帮忙。

更新:解决方案 - 谢谢@K.Davis!

Sub HideRowsSecond()

    Dim srcRng As Range, ws As Worksheet
    Set ws = ActiveSheet
    Set srcRng = ws.Rows("5:" & ws.Cells(ws.Rows.Count, 4).End(xlUp).Row)

    Dim R As Range, hideRng As Range
    For Each R In srcRng
        If Application.CountA(R.Columns("H:BN").SpecialCells(xlCellTypeVisible)) = 0 Then
            If hideRng Is Nothing Then
                Set hideRng = R.EntireRow
            Else
                Set hideRng = Application.Union(hideRng, R.EntireRow)
            End If
        End If
    Next R

    If Not hideRng Is Nothing Then hideRng.EntireRow.Hidden = True
    MsgBox ("Complete")
End Sub

Answer 1

您可以使用预建的CountA()工作表函数来计算其中包含数据的单元格的数量。此计数将返回值 0如果整行是空白的。

我会使用 UsedRange所以你不会不必要地计算过去的数据。我还将联合范围隐藏并一次隐藏它们，而不是一次一个，以获得更好的性能。

Sub HideRows()

    Dim r As Range, hideRng As Range

    For Each r In ActiveSheet.UsedRange.Rows
        If Application.CountA(r.EntireRow) = 0 Then
            If hideRng Is Nothing Then
                Set hideRng = r.EntireRow
            Else
                Set hideRng = Application.Union(hideRng, r.EntireRow)
            End If
        End If
    Next r

    If not hideRng is nothing then hideRng.EntireRow.Hidden = True

End Sub

您不需要使行的隐藏过于复杂。这将在不循环的情况下取消隐藏您的行。

Sub UnHideRows()

    ActiveSheet.Cells.EntireRow.Hidden = False

End Sub

感谢 Chris Neilsen 指出您可能只想检查可见列是否有任何数据，您可以使用它来代替第一个代码:

Sub HideRows()

    Dim r As Range, hideRng As Range

    UnHideRows

    For Each r In ActiveSheet.UsedRange.Rows
        If Application.CountA(r.EntireRow.SpecialCells(xlCellTypeVisible)) = 0 Then
            If hideRng Is Nothing Then
                Set hideRng = r.EntireRow
            Else
                Set hideRng = Application.Union(hideRng, r.EntireRow)
            End If
        End If
    Next r

    If Not hideRng Is Nothing Then hideRng.EntireRow.Hidden = True

End Sub

根据 OP 的响应，只想查看 H:BN 列之间的可见行，您可以使用以下方法来完成此操作:

Sub HideRows()

    Dim srcRng As Range, ws As Worksheet
    Set ws = ActiveSheet
    Set srcRng = ws.Rows("1:" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)

    UnHideRows

    Dim r As Range, hideRng As Range
    For Each r In srcRng
        If Application.CountA(r.Columns("H:BN").SpecialCells(xlCellTypeVisible)) = 0 Then
            If hideRng Is Nothing Then
                Set hideRng = r.EntireRow
            Else
                Set hideRng = Application.Union(hideRng, r.EntireRow)
            End If
        End If
    Next r

    If Not hideRng Is Nothing Then hideRng.EntireRow.Hidden = True

End Sub

在这个函数中，我继续创建了一个新的 Range 变量 srcRng , 因为使用 Columns()功能与 UsedRange有时会导致问题。由于我们不再使用 UsedRange ，我们现在找到最后一个使用行 ws.Cells(ws.Rows.Count, 1).End(xlUp).Row .