VBA实现多工作表指定单元格值变化时自动控制行显隐

Hey there! Let's get your VBA code working exactly how you need it—showing row 54 on the "6. Local" sheet only when the total of your specified cells is greater than 0, and hiding it when that total drops to 0.

The Problem with Your Current Code #

Your existing snippet only checks individual cells one by one, and only shows the row when a single cell is updated to a positive value. It doesn't handle critical cases like:

• You set D16 to 5 (row shows), then later set D16 back to 0 but D31 is still 3 (row should stay visible, but your code won't update it)
• You need to add more target cells down the line without rewriting half the code

Improved Code Solution #

Here's a robust version that checks the sum of all your target cells every time one of them is modified:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Define all your target cells here—add or remove addresses as needed
    Dim targetCells As Range
    Set targetCells = Union( _
        Me.Range("$D$16"), _
        Me.Range("$D$31") _
        ' Add more cells like: Me.Range("$D$46"), _
    )
    
    ' Only run the check if the changed cell is one of our targets
    If Not Intersect(Target, targetCells) Is Nothing Then
        Dim totalSum As Double
        totalSum = Application.WorksheetFunction.Sum(targetCells)
        
        ' Toggle row visibility: hide if sum is 0 or less, show if sum > 0
        Sheets("6. Local").Rows("54").EntireRow.Hidden = (totalSum <= 0)
    End If
End Sub

Key Features Explained #

• Union: Groups all your target cells into a single range, making it trivial to add/remove cells later without rewriting logic.
• Intersect: Ensures we only run the sum check when one of our target cells is modified (saves unnecessary processing for unrelated cell changes).
• Sum-based visibility: Instead of checking each cell individually, we calculate the total sum. If it's ≤0, hide the row; if it's >0, show it. This covers every scenario, even when multiple cells are adjusted at once.

How to Use This #

1. Open your Excel file and press Alt + F11 to open the VBA Editor.
2. Find the worksheet that contains your target cells (D16, D31, etc.) in the Project Explorer on the left.
3. Double-click that worksheet to open its code module, then paste the code above.
4. Add any additional target cells to the Union line (follow the comment example).
5. Save your file as a .xlsm (Macro-Enabled Workbook) so the code stays active.

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