Autofill Macro Needed

[Graham Hill]

I have several rows of text which look as follows:

LONDON
eng
eng

GLASGOW
sco
sco

I need a macro to autofill from the top row and then skip the missing cell to autofill the next block. This list is about 5000 rows so the macro will have to always find the next block and fill it until there is no more data.

 

[AJ]

Hi Graham

Would this do the trick??

Assuming the Column in question is Column A and data starts in A1 and the first data item is something we would want to copy down...

Sub test()

Lastrow = Range("A65536").End(xlUp).Row

Range("A1").Select

Do While ActiveCell.Row < Lastrow + 1

If ActiveCell.Value <> "" And ActiveCell.Offset(1, 0).Value <> "" Then
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

End Sub

 

[Graham Hill]

Thanks, that is absolutley perfect!