Create macro to reformat spreadsheet

[Ecovindaloo]

I need to create macro to reformat spreadsheet for importing into
Access. As usual the spreadsheet is not formatted properly.


Here is a piece of the spreadsheet:
3196

28320221EBLADENST 39294812
275813701MABLELANE 40189085

3200

601771294ANGELINEDRIVE 40240796
60136321JOHNMBOORDR 39278689

3207

271062990CARRIAGEDRRAMSGAT 39345882
271065981OLDPLANKRD 39456725

What I need to do is create a macro to add a column and then take the
ID Number (i.e. 3169) and put it in those rows below. Then delete the
row with the ID Number and blank rows. Is there any easy way to do
this in a macro?

Thanks in advance for the help.

 

[Dave Peterson]

Maybe you don't need a macro.

I'm guessing that the 3196 entries are in column A and the other rows are in
columns A and B.

If that's true, then try this:
Insert a new column A
Put this formula in A1: =B1
Put this formula in A2: =IF(COUNTA(B2:C2)=1,B2,A1)
Drag the formula in A2 down as far as you need.

Now convert column A to values (edit|copy, edit|Paste special|values)

Then filter by column C to show just the blank rows.
Delete the visible rows

(Show all the data if you need to.)