如何通过VBA依据上一行内容自动隐藏/取消隐藏行？

Absolutely! This is totally achievable with VBA, and it’ll handle both your first 10-row block and the second one below it perfectly. Let’s walk through the solution step by step.

First, let’s lock in the rules you want to enforce:

• First block (Rows 1-10):
  • Rows 1 and 2 stay visible at all times
  • If Row 1 is empty → hide Rows 3 through 10
  • If Row 1 has any content → unhide Row 3
  • If Row 2 has any content → unhide Row 4
  • Continue this pattern: Row n (3 ≤ n ≤10) unhides if Row n-2 has content
• Second block (we’ll assume Rows 11-20; adjust row numbers if yours differ):
  • Rows 11 and 12 stay visible always
  • Same logic applies: Row m (13 ≤ m ≤20) unhides if Row m-2 has content; if Row 11 is empty, hide Rows 13-20

We’ll use a Worksheet_Change event so the hiding/unhiding happens automatically whenever you edit cells in the trigger rows. Here’s the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Disable events temporarily to avoid infinite loops
    Application.EnableEvents = False
    
    ' --------------------------
    ' Handle first block (Rows 1-10)
    ' --------------------------
    Dim i As Integer
    ' Force Rows 1-2 to stay visible
    Rows("1:2").EntireRow.Hidden = False
    
    ' Check if Row 1 has no content
    If WorksheetFunction.CountA(Rows(1)) = 0 Then
        ' Hide all rows from 3 to 10
        Rows("3:10").EntireRow.Hidden = True
    Else
        ' Loop through rows 3-10, toggle visibility based on the row two above
        For i = 3 To 10
            Rows(i).EntireRow.Hidden = (WorksheetFunction.CountA(Rows(i - 2)) = 0)
        Next i
    End If
    
    ' --------------------------
    ' Handle second block (Rows 11-20) - adjust row numbers if needed!
    ' --------------------------
    Dim j As Integer
    ' Force Rows 11-12 to stay visible
    Rows("11:12").EntireRow.Hidden = False
    
    ' Check if Row 11 has no content
    If WorksheetFunction.CountA(Rows(11)) = 0 Then
        ' Hide all rows from 13 to 20
        Rows("13:20").EntireRow.Hidden = True
    Else
        ' Loop through rows 13-20, toggle visibility based on the row two above
        For j = 13 To 20
            Rows(j).EntireRow.Hidden = (WorksheetFunction.CountA(Rows(j - 2)) = 0)
        Next j
    End If
    
    ' Re-enable events so future edits trigger the code
    Application.EnableEvents = True
End Sub

1. Right-click the tab of your target worksheet (e.g., "Sheet1")
2. Select View Code from the dropdown menu
3. Paste the code into the blank module that opens
4. Close the VBA editor—you’re all set! Now any edits to the trigger rows will auto-adjust visibility.

• If your second block isn’t Rows 11-20, just swap out the row numbers in the second section (e.g., for Rows 21-30, use 21:22, 23:30, and 21 in the CountA check)
• If you only want to check a specific cell per row (like Column A), replace Rows(i - 2) with Cells(i - 2, 1) (change 1 to your target column number)
• The CountA function detects any content (text, numbers, formulas)—it’s perfect for your "any content" requirement

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