Excel VBA代码问题：填充同列等值单元格间空白未达预期

Let's break down why your original code isn't working, then fix it to match your exact requirements.

What's Wrong with the Original Code? #

Your current script has three critical issues that prevent it from meeting your needs:

1. Broken loop structure: You commented out the Do Until line but left the Loop at the end—this will either cause a dead loop or fail to run the logic correctly.
2. Incomplete logic: It only checks the immediately preceding cell in column A, completely ignoring your requirement to compare the previous nearest non-empty cell with the next nearest non-empty cell.
3. Unreliable Select/ActiveCell: Using these commands makes your code slower and prone to errors if you click elsewhere while it runs.

Corrected VBA Code #

This version implements your exact rule set:

Sub FillBlanksWithLogic()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    Dim prevNonEmptyRow As Long
    Dim nextNonEmptyRow As Long
    
    ' Target the worksheet with your data (change to Sheet1 if needed)
    Set ws = ActiveSheet
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    currentRow = 2 ' Start at row 2 (assuming row 1 is headers)
    Do While currentRow <= lastRow
        ' Only process blank cells in column A
        If ws.Range("A" & currentRow).Value = "" Then
            ' Find the closest non-empty row above the current blank
            prevNonEmptyRow = currentRow - 1
            Do While prevNonEmptyRow >= 1 And ws.Range("A" & prevNonEmptyRow).Value = ""
                prevNonEmptyRow = prevNonEmptyRow - 1
            Loop
            
            ' Find the closest non-empty row below the current blank
            nextNonEmptyRow = currentRow + 1
            Do While nextNonEmptyRow <= lastRow And ws.Range("A" & nextNonEmptyRow).Value = ""
                nextNonEmptyRow = nextNonEmptyRow + 1
            Loop
            
            ' Apply your fill rules
            If prevNonEmptyRow >= 1 And nextNonEmptyRow <= lastRow Then
                ' Both previous and next non-empty values exist
                If ws.Range("A" & prevNonEmptyRow).Value = ws.Range("A" & nextNonEmptyRow).Value Then
                    ws.Range("A" & currentRow).Value = ws.Range("A" & prevNonEmptyRow).Value
                Else
                    ws.Range("A" & currentRow).Value = "X"
                End If
            ElseIf prevNonEmptyRow >= 1 Then
                ' Only previous non-empty value exists (e.g., trailing blanks)
                ws.Range("A" & currentRow).Value = ws.Range("A" & prevNonEmptyRow).Value
            ElseIf nextNonEmptyRow <= lastRow Then
                ' Only next non-empty value exists (e.g., leading blanks)
                ws.Range("A" & currentRow).Value = ws.Range("A" & nextNonEmptyRow).Value
            End If
        End If
        currentRow = currentRow + 1
    Loop
End Sub

Key Improvements & Explanations #

• No more Select: We directly reference cells using the worksheet object, which is faster and more reliable than relying on active cells.
• Proper non-empty cell lookup: The code searches up and down to find the nearest non-empty cells, not just the immediate row above or below.
• Handles edge cases: It accounts for leading blanks (no previous non-empty value) and trailing blanks (no next non-empty value) by filling with the available value.
• Clear loop structure: The main loop runs through every row with data, so you won't hit unexpected dead loops.

Quick Tips Before Running #

1. Backup your file: Always make a copy of your Excel sheet before running VBA code to avoid accidental data loss.
2. Adjust the column: If your target data isn't in column A, replace all instances of "A" with your target column letter (e.g., "B").
3. Update the starting row: If your headers are in a different row (not row 1), change currentRow = 2 to match your first data row.

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