Excel VBA FillDown: How to Fill Blank Cells in a Column

Two different jobs share one name here, and mixing them up is what sends people looking for this page. FillDown is a copy operation. Filling the blanks in a column is a repair operation. The first will happily destroy your data while doing exactly what it was asked.

What Range.FillDown actually does

Microsoft’s own description is blunt: the method “fills down from the top cell or cells in the specified range to the bottom of the range”, and “the contents and formatting of the cell or cells in the top row of a range are copied into the rest of the rows in the range”.

Worksheets("Sheet1").Range("A1:A10").FillDown

A1 wins. A2 through A10 become copies of it — values, formulas and formatting alike. Nothing checks whether A5 already held something worth keeping.

It works on blocks too. Given Range("A1:D10").FillDown, the whole of row 1 is copied down through row 10. And because Selection is just a Range, Selection.FillDown is the same method aimed at whatever the user happens to have highlighted — which is why it shows up in recorded macros and why it is worth treating carefully in code someone else will run.

Used deliberately, this is useful: it is the VBA equivalent of pressing Ctrl+D, and it is the right call when you genuinely want one formula propagated down a column. It is simply not a blanks tool.

The macro you actually want

To fill only the empty cells, narrow the range to the empty cells before you write anything.

Sub FillBlanksDown()
    Dim rng As Range, filled As Range, a As Range

    ' Start at the first row of DATA, never at row 1.
    Set rng = ActiveSheet.Range("A2:A500")

    On Error Resume Next
    Set filled = rng.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    If filled Is Nothing Then Exit Sub

    filled.FormulaR1C1 = "=R[-1]C"

    ' Convert only the cells we just wrote, one area at a time.
    For Each a In filled.Areas
        a.Value = a.Value
    Next a
End Sub

SpecialCells(xlCellTypeBlanks) returns just the empty cells — the same selection you would get from Go To Special in the interface. Writing =R[-1]C into them puts a formula in each one pointing at the cell directly above. Keeping that subset in filled is what makes the last step safe: the loop replaces the formulas with their results only in the cells the macro wrote.

Converting the whole column instead, with rng.Value = rng.Value, is the version you will find all over the web, and it is a data-loss bug. That line flattens every cell in A2:A500, so any formula that was already sitting in a non-empty cell is silently replaced by whatever it happened to return — with no undo, because macro edits clear the undo stack. The loop runs area by area for a second reason: SpecialCells hands back a multi-area range, and .Value = .Value on one of those copies the first area over all the others.

Never start the range at row 1. =R[-1]C means “one row up”, so in A1 it points off the top of the sheet and returns #REF!. Starting at A2 is safe for the formula, but if A2 itself is blank the macro pulls your header down into the data — begin at the first row that genuinely holds data.

The cascade takes care of runs of blanks. The first gap cell reads the last real value above it; the second gap cell reads the first, which now holds that value; and so on down the run.

Why R1C1, and not “=A1”

This is the detail that breaks most attempts, and it is worth understanding rather than copying.

SpecialCells returns a multi-area range: a scattered collection of separate blocks, not one rectangle. When you assign an A1-style relative formula to a range, Excel translates it outwards from the top-left cell of what you assigned to. Across several disconnected areas that translation is not what you meant, and you get formulas pointing at arbitrary cells.

R1C1 notation sidesteps the problem entirely. =R[-1]C does not mean “one row above the first cell”; it means “one row above this cell, same column”, and it means that identically in every cell it lands in. The square brackets are what make it relative — R1C1 without them would be an absolute reference to cell A1.

A version you can read at a glance

If the one-liner feels too clever to maintain, the explicit loop does the same thing and is easier to modify later:

Sub FillBlanksDownLoop()
    Dim rng As Range, c As Range

    On Error Resume Next
    Set rng = ActiveSheet.Range("A2:A500").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "No blank cells in that range."
        Exit Sub
    End If

    Application.ScreenUpdating = False
    For Each c In rng
        c.Value = c.Offset(-1, 0).Value
    Next c
    Application.ScreenUpdating = True
End Sub

This writes values directly, so there is no conversion step. For Each walks the areas in order and the cells within each area top to bottom, which is exactly the order the cascade needs. On a large column, ScreenUpdating = False is the difference between instant and visibly slow.

Handle the no-blanks case

SpecialCells does not return an empty range when it finds nothing — it raises run-time error 1004. On a column with no gaps, an unguarded macro simply stops with a dialog in the user’s face.

That is what On Error Resume Next is doing in both examples above. In the loop version it is paired with an If rng Is Nothing check, which is the more honest pattern: catch the error, then test whether you actually got a range, and only afterwards switch error handling back on with On Error GoTo 0.

One more practical note: xlCellTypeBlanks is a built-in constant with the value 4. Inside Excel you can use the name. If you are driving Excel from another application through late binding, the names are not available and you pass the number instead.

The variants side by side

What you want	Code	Overwrites filled cells?
Copy the top cell down a column	Range("A1:A10").FillDown	Yes
Copy the top row of a block down	Range("A1:D10").FillDown	Yes
Same, on the user’s selection	Selection.FillDown	Yes
Fill only the gaps	.SpecialCells(4).FormulaR1C1 = "=R[-1]C"	No
Replace formulas with results, safely	For Each a In filled.Areas: a.Value = a.Value: Next a	No, only the cells it filled
Replace formulas with results, whole column	rng.Value = rng.Value	Yes — destroys existing formulas

Where Fill Down lives outside VBA

If you arrived here looking for the command rather than the method: Fill Down is on the Home tab, in the Editing group, under Fill → Down. The shortcut is Ctrl+D, which Microsoft documents as copying “the contents and format of the topmost cell of a selected range into the cells below” — the same overwriting behaviour as the VBA method, because it is the same command.

For a one-off repair you do not need a macro at all. Go To Special selects the blanks by hand in about eight seconds, and the step-by-step route for filling blank cells with the value above covers it, along with the Power Query version for data you reimport every week. Reach for the macro when you have many columns, many sheets or many files — the point at which doing it by hand stops being eight seconds.

If this is your first macro on a real workbook, the same cautions apply as to any VBA that writes in bulk: run it on a copy, and remember that macro edits cannot be undone with Ctrl+Z. The notes on that in putting borders around all used cells are worth a look, and deleting all pictures or charts on a worksheet uses the same SpecialCells pattern for a different object type.

Frequently asked questions

Sources