Macro required - Filling in blanks

[walker2011au]

Hi Guys, wondering if you could help me with a macro, or if there is a way to apply a formula to do the same.. It would be okay if it was only a few records I could just drag date down etc, but its 30,000 lines..
So need a macro to copy the date until a new one is found and stop..


Current Data:

|Payment Date| |Reference|
12-09-2007---- 1
----------------2
----------------3
----------------4
13-09-2007 ----5
----------------6
----------------7

Desired Result:

|Payment Date|-|Reference|
12-09-2007 -----1
12-09-2007 -----2
12-09-2007 -----3
12-09-2007 -----4
13-09-2007 -----5
13-09-2007 -----6
13-09-2007 -----7


Cheers!

 

[Sektor]

1. Select column with Payment Day.
2. F5.
3. Special...
4. Blanks.
5. Write formula: =A1 (where A1 is the first cell with data in Payment Day).
6. Ctrl+Enter.

 

[Dazzawm]

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

Try this on a copy of your workbook.

 

[walker2011au]

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

Try this on a copy of your workbook.

works a treat! much appreciated.

Thanks sektor, doesnt quite do what I needed, but handy to know, will use the f5 blank selector in future for sure.