基于Sheet1单元格值控制Sheet2行隐藏与显示问题求助

Got it, let's work through this problem—you've been stuck on it for a week, so let's get you sorted. The core need is to automatically hide rows in Sheet2 when specific cells in Sheet1 are empty, and show them when those cells have content. Since Excel formulas can't directly control row visibility, we'll use VBA (Visual Basic for Applications) to make this happen reliably.

Step 1: Create the Macro to Update Row Visibility #

First, we'll write a macro that checks the target cells in Sheet1 and adjusts Sheet2's rows accordingly.

1. Open the VBA Editor: Press Alt + F11 (or go to the Developer tab > Visual Basic if you have it enabled).
2. Insert a Module: Right-click your workbook name in the Project Explorer > Insert > Module.
3. Paste this code into the module:

Sub UpdateSheet2Rows()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim targetRow As Long
    Dim sheet1CellRow As Long
    
    ' Set references to your sheets (change names if yours are different)
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    ' Speed up the macro by turning off screen flickering
    Application.ScreenUpdating = False
    
    ' Start fresh by unhiding all rows in Sheet2 (avoids stuck hidden rows)
    ws2.Rows.EntireRow.Hidden = False
    
    ' Loop through rows in Sheet2 (adjust the "1 To 10" to match how many rows you need to handle)
    For targetRow = 1 To 10
        ' Map Sheet2 row to the corresponding Sheet1 A-column cell
        Select Case targetRow
            Case 1
                sheet1CellRow = 1 ' Sheet2 Row 1 ↔ Sheet1 A1
            Case Else
                sheet1CellRow = 5 * (targetRow - 1) ' Sheet2 Row 2 ↔ A5, Row3 ↔ A10, etc.
        End Select
        
        ' Check if the Sheet1 cell is empty
        If IsEmpty(ws1.Cells(sheet1CellRow, "A")) Then
            ' Hide the matching row in Sheet2
            ws2.Rows(targetRow).EntireRow.Hidden = True
        Else
            ' Show the row if the cell has content
            ws2.Rows(targetRow).EntireRow.Hidden = False
        End If
    Next targetRow
    
    ' Turn screen updating back on
    Application.ScreenUpdating = True
    
    ' Optional: Confirmation message
    MsgBox "Sheet2 row visibility updated!", vbInformation
End Sub

How This Works: #

• We first link to Sheet1 and Sheet2 so the code knows where to look.
• Disabling ScreenUpdating makes the macro run faster without distracting screen flicker.
• Unhiding all rows first ensures we don't have rows stuck hidden if your data changes.
• The Select Case handles the mapping: Sheet2 Row 1 checks Sheet1 A1, Row 2 checks A5, Row3 checks A10, and so on (since 5*(2-1)=5, 5*(3-1)=10, etc.).
• For each row, we use IsEmpty() to check if the Sheet1 cell is blank, then hide/show the Sheet2 row accordingly.

Step 2: Make It Automatic (Optional) #

If you want the row visibility to update automatically when you edit the target cells in Sheet1, add this event code to Sheet1's code module:

1. In the VBA Editor, double-click "Sheet1" in the Project Explorer.
2. Paste this code into the code window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' List of target rows in Sheet1 that trigger an update (add more as needed)
    Dim targetRows As Variant
    targetRows = Array(1, 5, 10, 15, 20)
    
    ' Check if the edited cell is in Column A and one of our target rows
    If Not Intersect(Target, Me.Columns("A")) Is Nothing Then
        If UBound(Filter(targetRows, Target.Row)) > -1 Then
            ' Run the update macro
            UpdateSheet2Rows
        End If
    End If
End Sub

How This Works: #

• This event triggers every time you edit a cell in Sheet1.
• It checks if the edited cell is in Column A and matches one of your target rows (1,5,10,15...).
• If it does, it automatically runs the UpdateSheet2Rows macro to adjust Sheet2's rows.

Important Notes #

• Save as Macro-Enabled Workbook: You must save your file as .xlsm (not .xlsx) to keep the VBA code.
• Adjust Ranges: If you have more target rows, update the For targetRow = 1 To 10 line in the first macro, and add the Sheet1 row numbers to the targetRows array in the event code.
• Test First: Run the UpdateSheet2Rows macro manually first (press F5 in the VBA Editor) to make sure the row mapping works as expected.

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