Excel VBA：下拉框选N/A时批量设值并隐藏行求助

Got it, let's tackle this problem step by step. Your core need is to automatically set corresponding E-column cells to N/A and hide the rows when the dropdown in the trigger cell is set to N/A, then unhide the rows when other options are selected. Here's a clean, maintainable solution using VBA:

Step 1: Use a Dictionary to Map Trigger Cells to Target Ranges #

First, we'll create a dictionary to store the relationship between each trigger cell (like G13) and its corresponding target row range. This makes the code easy to update if you add more trigger cells later.

Step 2: Full VBA Code Implementation #

Replace your existing Worksheet_Change sub with this complete code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Disable events to prevent infinite loops when modifying cells
    Application.EnableEvents = False
    
    ' Define the mapping of trigger cells to target row ranges
    Dim triggerMap As Object
    Set triggerMap = CreateObject("Scripting.Dictionary")
    
    ' Add all your trigger-cell to row-range mappings here
    triggerMap("G13") = "14:22"
    triggerMap("G23") = "24:24"
    triggerMap("G25") = "26:27"
    triggerMap("G28") = "29:30"
    triggerMap("G31") = "32:34"
    triggerMap("G35") = "36:38"
    triggerMap("G39") = "40:41"
    triggerMap("G42") = "43:44"
    triggerMap("G45") = "46:49"
    triggerMap("G50") = "51:54"
    triggerMap("G55") = "56:57"
    triggerMap("G58") = "59:61"
    triggerMap("G62") = "63:68"
    triggerMap("G69") = "70:83"
    triggerMap("G84") = "85:87"
    triggerMap("G88") = "89:97"
    triggerMap("G98") = "99:104"
    triggerMap("G105") = "106:111"
    triggerMap("G112") = "113:115"
    triggerMap("G116") = "117:118"
    triggerMap("G119") = "120:124"
    triggerMap("G125") = "126:128"
    triggerMap("G129") = "130:137"
    triggerMap("G138") = "139:145"
    triggerMap("G146") = "147:147"
    
    ' Check if the changed cell is one of our trigger cells
    If triggerMap.Exists(Target.Address(False, False)) Then
        Dim targetRows As Range
        Set targetRows = Me.Range(triggerMap(Target.Address(False, False)))
        
        If Target.Value = "N/A" Then
            ' Set E-column cells in target rows to N/A
            Me.Range("E" & targetRows.Row & ":E" & targetRows.Row + targetRows.Rows.Count - 1).Value = "N/A"
            ' Hide the target rows
            targetRows.EntireRow.Hidden = True
        Else
            ' Unhide the target rows
            targetRows.EntireRow.Hidden = False
        End If
    End If
    
    ' Re-enable events
    Application.EnableEvents = True
End Sub

Key Explanations: #

• Event Disabling: Application.EnableEvents = False prevents the Worksheet_Change event from firing again when we modify the E-column cells, avoiding infinite loops.
• Dictionary Mapping: The triggerMap dictionary lets you easily add/remove trigger-cell pairs without rewriting messy conditional logic.
• Target Range Handling: For each trigger cell, we get the corresponding row range, then update the E-column cells in that range only when N/A is selected.
• Row Visibility: We toggle row visibility based directly on the dropdown selection.

How to Use: #

1. Open your Excel workbook.
2. Press Alt + F11 to open the VBA Editor.
3. Find your worksheet in the Project Explorer (left pane).
4. Double-click the worksheet to open its code window.
5. Paste the code above, replacing any existing Worksheet_Change sub.
6. Save the workbook as a .xlsm file (macro-enabled workbook) to retain the code.

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