- Select
A1:A8
. - Press
F5
to show the Goto dialog. - Click Special ….. Select Blanks and click OK.
That will select a noncontiguous range of blank cells.
- Then, without selecting anything else, type
=A3
and press control+enter. - That will enter an array formula in all the blank cells referring to the cell above it.
- Reselect
A1:A8
, and Edit – Copy. - Then Edit – Paste Special – Values. And you’re all set.
Note that the =A3
refers to the cell above the first blank cell.
If you want to do it with a macro, you could loop through the cells and fill in the empty ones.
Public Sub FillBlanks()
Dim rColumn As Range
Dim rCell As Range
If TypeName(Selection) = "Range" Then
For Each rColumn In Selection.Columns
For Each rCell In rColumn.Cells
If rCell.Row > rColumn.Cells(1).Row Then
If IsEmpty(rCell.Value) Then
rCell.Value = rCell.Offset(-1).Value
End If
End If
Next rCell
Next rColumn
End If
End Sub