Excel VBA实现指定单元格区域内仅保留当前输入单元格数据

Hey there, I know you've spent hours hunting for a solution—let's get this sorted for you right away! This VBA code will make sure that whenever you enter data into any cell in B4:B12, all other cells in that range get cleared automatically, leaving only your newly entered data intact.

Here's how to set it up: #

1. Open your Excel file, then right-click on the worksheet tab (the one where you want this functionality to work) and select View Code.
2. In the VBA editor window that pops up, paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Define the target range we need to monitor
    Dim targetRange As Range
    Set targetRange = Me.Range("B4:B12")
    
    ' Check if the modified cell is within our target range
    If Not Intersect(Target, targetRange) Is Nothing Then
        ' Turn off event triggers temporarily to avoid infinite loops
        Application.EnableEvents = False
        
        ' Save the value the user just entered
        Dim inputValue As Variant
        inputValue = Target.Value
        
        ' Clear all cells in the target range
        targetRange.ClearContents
        
        ' Put the saved value back into the cell the user edited
        Target.Value = inputValue
        
        ' Re-enable events so future changes work as expected
        Application.EnableEvents = True
    End If
End Sub

Let me break down what this code does: #

• Worksheet_Change event: This triggers automatically whenever any cell on the worksheet is modified.
• Intersect check: We first confirm the changed cell is inside B4:B12—no need to run the code if someone edits a cell outside this range.
• Application.EnableEvents = False: This is critical! It stops the code from triggering itself again when we clear other cells (since clearing cells counts as a "change" too).
• Storing and restoring the input value: We save what you just typed, clear the entire range, then put your input back—this guarantees only the current cell has data, no matter what was there before.

Quick tips to keep in mind: #

• Make sure you paste this code into the worksheet module (not a standard module). That's why we right-clicked the sheet tab earlier—this ensures the event only runs for that specific sheet.
• If you need this behavior on multiple sheets, just repeat the setup process for each sheet, or let me know and I can share a workbook-level version.
• Test it out: Type something in B5, then type in B7—you'll see B5 clears immediately. Type in B10 next, and B7 will clear, leaving only B10 with data.

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