Messy excel worksheet needs reorganizing

[PhilDesanex]

We have inherited a lapsed customer list that we want to use for some marketing to tell them about our new website. The list has been made on an excel sheet but not in a proper worksheet layout. I have five of these worksheets so a manual correction will take me forever. I would like to know if there is an automated way to convert these records into a correct table layout. In my illustration below, Column A contains a customer code. Column B contains an address which lays out down the sheet rather than across into columns C onwards. The address can be 4 or 5 lines but there is always a blank line between the individual customer details. Is there a macro I could use to parse through the worksheet and move the address lines onto the same row as the customer code, as per a normal Excel layout? Any help would be appreciated. Is there some way to detect the combination of a blank cell/non-blank cell in column A that would help to determine when to start the next record?

ABAC	Abacus Taxis
	2, Any Street
	Anytown
	Any County
	Any Postcode
BRAV	Bravo taxis
	14 Another Street
	Another County
	Another postcode
CITY	City Cabs

 

[Fluff]

How about

Sub PhilDesanex()
   Dim Rng As Range
   
   For Each Rng In Range("B:B").SpecialCells(xlConstants).Areas
      Rng.Resize(1, Rng.Count).Value = Application.Transpose(Rng)
   Next Rng
   Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[PhilDesanex]

Fluff, thanks for that. How do I use that code and what will it do?

 

[Fluff]

Put it into a standard module & when you run it, it will work on the active sheet. After it has run you will get single rows for each customer.

 

[PhilDesanex]

Fluff, that bit of code worked brilliantly. Thanks. I'm posting another problem which is hopefully straightforward.

 

[Fluff]

You're welcome & thanks for the feedback.