AutoFill Blank cells with closest above data

[raynerfredrick]

Looking for a to solution to pull data down into blank cells below until no blank cells remain. I could obviously just grab each cell and complete this but I've got 600 unique data pieces with varying numbers of blank cells below which need to be filled. Thanks so much.

AutoZone

Bridgestone

American Can

Primrose Bank

Johns Hopkins

Community Maverick

First Exchange

<tbody>
</tbody>

 

[JackDanIce]

Manual suggestion:

- First blank cell (pretend it's A3) enter formula: =A2
- Copy A3
- Select column A, press F5, click Special, select Blanks then ok
- Paste
- Select Column A, copy and paste values over it

Code:

Sub Fill()
Dim x As Long
x = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1").Resize(x)
  .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  .Value = .Value
End With
End Sub

 

[raynerfredrick]

Dude, brilliant. Thanks so much.

 

[JackDanIce]

You're welcome