vba 使用相同按钮隐藏和取消隐藏空白行

Hide and Unhide Blank Rows With the Same Button

提问by jmaz

I have written the below code to hide blank rows within the used range of a worksheet. The code works perfectly fine. I have assigned this macro to a button on the worksheet. Clicking the button hides the blank rows within the used range.

我编写了以下代码来隐藏工作表使用范围内的空白行。该代码工作得很好。我已将此宏分配给工作表上的一个按钮。单击该按钮可隐藏使用范围内的空白行。

Question: How do I modify the code so that clicking this same button does the reverse? If the blank rows are unhidden, then clicking the button hides them; and if they are hidden, then clicking the same button unhides them? I want to execute both procedures with one button.

问题：如何修改代码以便单击相同的按钮进行相反的操作？如果空白行未隐藏，则单击按钮隐藏它们；如果它们是隐藏的，那么单击相同的按钮会取消隐藏它们吗？我想一键执行这两个程序。

Sub HideLLRows()
'This sub un/hides blank rows in EIRP LL

Application.ScreenUpdating = False

Dim LastRow As Long
Set EIRPLL = Sheets("EIRP LL")

LastRow = EIRPLL.UsedRange.Rows.Count

For i = 6 To LastRow
    If EIRPLL.Range("B" & i) = "" Then
        EIRPLL.Rows(i).Hidden = True
    End If
Next

Application.ScreenUpdating = True
End Sub

回答by chris neilsen

The simple answer is to togglethe Hiddenstate of each blank row as you find it

简单的答案是在找到每个空白行时切换它的Hidden状态

EIRPLL.Rows(i).Hidden = Not EIRPLL.Rows(i).Hidden

This has the disadvantage that if the user changes the hidden state of one or more blank rows then this macro will not get you back to all hidden or all visable.

这样做的缺点是，如果用户更改了一个或多个空白行的隐藏状态，那么此宏将不会让您回到所有隐藏或所有可见的状态。

The alternative is to set the visibility based on the first blank found.

另一种方法是根据找到的第一个空白设置可见性。

Here's your code refactored, with some additional optimisations:

这是您重构的代码，并进行了一些额外的优化：

• Dimallyour variables
• Loop a varaint array, not a range. This is muchfaster
• Set the Hiddenproperty of all rows in one go

• Dim你所有的变量
• 循环变量数组，而不是范围。这要快得多
• Hidden一次性设置所有行的属性

---

---

Sub HideLLRows()
    'This sub un/hides blank rows in EIRP LL

    Application.ScreenUpdating = False
    Dim i As Long
    Dim EIRPLL As Worksheet
    Dim NewState As VbTriState
    Dim dat As Variant
    Dim rws As Range

    Dim LastRow As Long
    Set EIRPLL = Sheets("EIRP LL")

    With EIRPLL.UsedRange
        LastRow = .Rows.Count - .Row + 1 ' in case used range doesn't start at row 1
        dat = .Columns(2).Resize(LastRow, 1)
    End With

    NewState = vbUseDefault
    With EIRPLL
        For i = 6 To LastRow
            If dat(i, 1) = "" Then
                If NewState = vbUseDefault Then
                    NewState = Not .Rows(i).Hidden
                End If
                If rws Is Nothing Then
                    Set rws = Cells(i, 1)
                Else
                    Set rws = Union(rws, Cells(i, 1))
                End If
            End If
        Next
    End With
    rws.EntireRow.Hidden = NewState

    Application.ScreenUpdating = True
End Sub