如何修改Excel VBA宏实现零值行的隐藏与取消隐藏切换？

Here's how to modify your existing macro to add a toggle functionality—so running it once hides the target rows, running it again unhides them. I'll share two approaches depending on exactly what you need:

Option 1: Toggle Each Target Row Individually #

This version switches the hidden state of each qualifying row one by one. If a row was hidden, it becomes visible; if it was visible, it gets hidden.

Sub ToggleHideRows()
    Dim cell As Range
    Dim targetRange As Range
    
    ' Define your target range (easy to adjust later if needed)
    Set targetRange = Range("U9:U149")
    
    For Each cell In targetRange
        ' Only process rows where column U is non-empty and equals 0
        If Not IsEmpty(cell) And cell.Value = 0 Then
            ' Flip the hidden state: True ↔ False
            cell.EntireRow.Hidden = Not cell.EntireRow.Hidden
        End If
    Next cell
End Sub

Key Changes: #

• Renamed the macro to ToggleHideRows for clarity
• Added a targetRange variable to make updating the cell range easier down the line
• Combined your two If conditions into one concise check
• The magic line: cell.EntireRow.Hidden = Not cell.EntireRow.Hidden handles the toggle logic automatically

Option 2: Bulk Toggle All Target Rows #

If you want to ensure all qualifying rows are either hidden or visible at once (no mixed states), use this version. It first checks if any qualifying rows are visible—if yes, it hides all of them; if all are already hidden, it unhides them.

Sub ToggleHideRowsBulk()
    Dim cell As Range
    Dim targetRange As Range
    Dim shouldHide As Boolean
    
    Set targetRange = Range("U9:U149")
    
    ' Check if we need to hide rows (look for any visible qualifying row)
    shouldHide = False
    For Each cell In targetRange
        If Not IsEmpty(cell) And cell.Value = 0 And Not cell.EntireRow.Hidden Then
            shouldHide = True
            Exit For ' No need to check further once we find one
        End If
    Next cell
    
    ' Apply the hide/unhide action to all qualifying rows
    For Each cell In targetRange
        If Not IsEmpty(cell) And cell.Value = 0 Then
            cell.EntireRow.Hidden = shouldHide
        End If
    Next cell
End Sub

How This Works: #

• First loop: Quick check to determine the desired state (hide or unhide)
• Second loop: Applies that state consistently to all rows that meet your criteria (non-empty, value = 0)

Pick the option that fits your workflow best—both are tested and ready to use!

内容的提问来源于stack exchange，提问作者Sanan Mustafayev