Manual method......
Select the column then F5>Special>Blanks>OK.
Enter an = sign in top blank cell then point to cell above.
Hit CRTL + ENTER to replicate down the column.
When happy, copy>paste special>values>OK>Esc.
VBA macro method..........
Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long
Set wks = ActiveSheet
With wks
Col = ActiveCell.Column
'or
'col = .range("b1").column
Set Rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If
'replace formulas with values
With .Cells(1, Col).EntireColumn
.Value = .Value
End With
End With
End Sub
Gord Dibben Excel MVP
Is there a way to program all empty cells in a column to be filled with text
copied from the cell above each empty cell?
