Excel空白单元格填充需求：B列空白时复制对应A列值

Hey there! Let's fix that Excel issue you're dealing with. You need to fill empty cells in Column B with the matching value from Column A, while leaving any existing values in B untouched—totally doable with a few easy methods:

Method 1: Basic IF Formula #

This is the simplest approach for one-off tasks:

1. Pick an empty column next to B (say Column C, starting at C1)
2. Enter this formula: =IF(B1="",A1,B1)
3. Drag the fill handle (the tiny square at the bottom-right of the cell) all the way down to cover your data range
4. Once Column C has all the correct values, select the entire C column, copy it (Ctrl+C)
5. Right-click the top cell of Column B, choose Paste Values (this replaces the original B column with the filled data without keeping the formulas)

Method 2: Go To Special (No Formulas Needed) #

If you prefer not to use formulas, this quick trick works like a charm:

1. Select the entire Column B
2. Press Ctrl+G to open the Go To dialog, then click Special
3. In the Go To Special window, select Blanks and hit OK—all empty cells in B will be highlighted
4. Now, type =A followed by the row number of the first selected blank cell (e.g., if the first blank is B4, type =A4)
5. Press Ctrl+Enter instead of just Enter—this will automatically fill all selected blank cells with their corresponding A column values in one go!

Method 3: VBA Macro (For Repeat Tasks) #

If you need to do this regularly or have a huge dataset, a macro will save you time:

1. Press Alt+F11 to open the VBA Editor
2. Right-click your workbook in the Project Explorer, select Insert > Module
3. Paste this code into the module:

Sub FillBlankBWithA()
    Dim ws As Worksheet
    Set ws = ActiveSheet ' Replace with your sheet name if needed, e.g., Set ws = ThisWorkbook.Worksheets("Sheet1")
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To lastRow
        If ws.Cells(i, "B").Value = "" Then
            ws.Cells(i, "B").Value = ws.Cells(i, "A").Value
        End If
    Next i
End Sub

4. Press F5 to run the macro, or assign it to a button on your worksheet for easy access later.

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