求助：基于Excel关联单元格实现行隐藏的VBA解决方案

Hey there! As someone who's worked through plenty of Excel VBA row-hiding headaches, let's get this sorted for you. Here's a reliable approach that addresses both linking the columns and hiding the empty rows, plus we'll cover why your previous attempts might have failed.

Step 1: Ensure Sheet2 Column B is Linked to Sheet1 Column D #

First, make sure Sheet2's B3:B192 is pulling values from Sheet1's D3:D192. If you haven't set this up yet, enter this formula in cell B3:

=Sheet1!D3

Then drag the fill handle down to B192—this keeps the two ranges linked automatically as values in Sheet1 change.

Step 2: VBA Code to Hide Empty Rows in Sheet2 #

This code loops through each cell in your target range, checks for empty values (including formula-generated blanks), and hides the corresponding rows. It's written to be easy to follow for new VBA users:

Sub HideEmptyRowsInSheet2()
    Dim ws As Worksheet
    Dim cell As Range
    Dim targetRange As Range
    
    ' Define the worksheet we're working with
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    
    ' Set the range to check: B3 to B192
    Set targetRange = ws.Range("B3:B192")
    
    ' Unhide all rows first to reset any previous hiding
    targetRange.EntireRow.Hidden = False
    
    ' Loop through each cell in the range
    For Each cell In targetRange
        ' Check for empty values (handles both blank cells and formula results)
        If Trim(cell.Value) = "" Then
            ' Hide the entire row of this cell
            cell.EntireRow.Hidden = True
        End If
    Next cell
End Sub

Step 3: How to Use This Code #

1. Open your Excel file.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the left-side Project Explorer > Insert > Module.
4. Paste the code above into the new module.
5. Press F5 to run the macro, or close the editor and assign it to a button for quick access later.

Why Your Previous Solutions Might Have Failed #

• Incorrect Range: Maybe you targeted the wrong rows/columns (e.g., starting at B2 instead of B3, or going beyond row 192).
• Empty Value Check Issues: If your B column uses formulas, IsEmpty(cell) won't detect formula-generated blanks—using Trim(cell.Value) = "" covers both actual blanks and formula results.
• Missing Worksheet Reference: You might have forgotten to explicitly specify Sheet2, causing the code to run on the active sheet instead.

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