Fill blanks down column with cell above

[KtP]

I have a column which is 40,000+ cells in length. Top cell has text then
numerous blanks (1-100) then another text cell followed by blanks, etc. etc.
All of the text cells are different and I want to fill the blanks with last
text cell above.
It is too much to manually go through and paste (double-click bottom right
corner), I'm wondering if there is an easier way?

 

[Gary''s Student]

Here is a macro for column E, adjust it for your column of choice:

Sub copy_down()
Dim r As Range, rr As Range, n As Long
With ActiveSheet.UsedRange
n = .Rows.Count + .Row - 1
End With
Set r = Range(Cells(1, "E"), Cells(n, "E")).SpecialCells(xlCellTypeBlanks)
For Each rr In r
rr.FillDown
Next
End Sub

 

[KtP]

Wow, thank you! What a lifesaver

Here is a macro for column E, adjust it for your column of choice:

Sub copy_down()
Dim r As Range, rr As Range, n As Long
With ActiveSheet.UsedRange
n = .Rows.Count + .Row - 1
End With
Set r = Range(Cells(1, "E"), Cells(n, "E")).SpecialCells(xlCellTypeBlanks)
For Each rr In r
rr.FillDown
Next
End Sub