VBA: How to Fill Blank Cells with Value Above

Often you may want to use VBA to fill blank cells in an Excel sheet with the values in the cells directly above them.

You can use the following basic syntax to do so:

Sub Fill_From_Above()
  With Range("A2:B" & Range("C" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

This particular example will fill in all blank cells starting from cell A2 through column B to the last row that has a value in column C.

The following example shows how to use this syntax in practice.

Example: Use VBA to Fill Blank Cells with Value Above

Suppose we have the following dataset in Excel that shows the points scored during various games by various basketball players:

Suppose we would like to fill in the blank cells in columns A and B with the values in the cells above them.

We can create the following macro to do so:

Sub Fill_From_Above()
  With Range("A2:B" & Range("C" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

When we run this macro, we receive the following output:

Notice that all blank cells in the Team and Player columns have been filled in with the appropriate value of the cells above them.

Note: We used xlBlanks to identify blank cells in the range and then used =R[-1]C to fill in the blanks with the value from the cell one row above it.

The end result is that we were able to fill in all blank cells with the value in the cells above them.