Tom’s Tutorials for Excel: Fill empty cells with value from above

Tom’s Tutorials for Excel: Fill empty cells with value from above.
Today we have great new tip from Tom Urtis complete with screen shots. Enjoy & Excel!

Step 1
Select the range you want to work with, in this example range A1:A13.

Step 2
Press the F5 key to show the Go To dialog box, then click the Special button.

Step 3
Select the option button next to Blanks, and click OK.

Step 4
Your selected range will now have all blank cells selected. In this example, cell A3 is the active cell because it is the first blank cell in that range.

Step 5
With cell A3 as the active cell, type the formula =A2 as shown in the next picture, and press Ctrl+Enter. That will fill in all blank cells with the value of the cell above it. As a final option, you can copy column A and paste special for values, to keep the filled-in values constant.

This VBA macro would accomplish the same task:

Sub FillCellsFromAbove()
Application.ScreenUpdating = False
On Error Resume Next
With Columns(1)
.SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
.Value = .Value
End With
Err.Clear
Application.ScreenUpdating = True
End Sub

Note, the reference to Columns(1) refers to column A. If it were column H you were working with, the code would have been written as “With Columns(8)”.
The Error bypass is included for cases when no blank cells exist in column A, such as if the macro were to be run twice in succession.
The optional step was taken for converting the formulas to values.