如果内容总和 = 零，如何隐藏 Excel 行并跳过空格？

Question

我有两个代码，如下，一个隐藏行，另一个根据其中包含的值的总和等于零来取消隐藏行。但是，此代码还隐藏了我不想要的空白行，因为它们是部分之间的间隔符。任何建议将不胜感激，如果有改进现有代码的建议，我也将不胜感激。

Sub HideRows()

Dim R As Long
Dim Rng As Range

    If Selection.Rows.Count > 1 Then
        Set Rng = Selection
    Else
        Set Rng = ActiveSheet.UsedRange
    End If

    For R = 1 To Rng.Rows.Count
        If Application.Sum(Range(Rng(R, 3), Rng(R, Rng.Columns.Count))) = 0# Then
        Rng.Rows(R).Hidden = True
        End If
    Next R

End Sub
Sub UnHideRows()

Dim R As Long
Dim Rng As Range

    If Selection.Rows.Count > 1 Then
        Set Rng = Selection
    Else
        Set Rng = ActiveSheet.UsedRange
    End If

    For R = 1 To Rng.Rows.Count
        If Application.Sum(Range(Rng(R, 3), Rng(R, Rng.Columns.Count))) = 0# Then
        Rng.Rows(R).Hidden = False
        End If
    Next R

End Sub

Answer 1

将每个方法中的 FOR 循环替换为此...。

myRange = Range(Rng(R, 3), Rng(R, Rng.Columns.Count))
For R = 1 To Rng.Rows.Count
  If Application.CountBlank(myRange) <> myRange.Cells.Count Then
    If Application.Sum(myRange) = 0# Then
      Rng.Rows(R).Hidden = True
    End If
Next R

它首先检查该行范围中的所有单元格是否均为空白，如果为真，则实质上跳过进一步处理。