Autofill from one cell down to the next none empty cell on the same column Macro

[ezpete]

Hi all,
I'm trying to figure out the best way to accomplish autofilling from one cell in column A down to the next none empty cell in column A, I'm admitedly a bit of a novice with Macros but have been able to get by recording what i need to do. The issue is that the none empty cells in column A arent always in the same row, and the recorded Macros don't work as i had hoped.
I also need the autofill to stop running when it reaches the final row of column B.

A	B	C
35	LOCATIONX
	JACK
567	LOCATIONX
	JIMMY
	SIMON
	PAUL
4567	LOCATIONX
	AIDEN
	RUTH
56889	LOCATIONX
	JEN
	KAREN
	VICKY
	HELEN
	BECKY
	SIMON
4567	TED

<tbody>
</tbody>

At present I have to do this manually and, as the locations in column B are contiunously being added to, is fast becoming very time consuming.

Help please!

 

[VoG]

Try

Sub test()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Range("A1:A" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

 

[ezpete]

Works perfectly, thank you so much!