Transposing 4-row clumps of name and address in Column A to Columns A,B,C,D

[kbird7]

I am having a problem figuring out a macro to transpose 6500 rows of
name & address in clumps of 4 rows each in column A separated by 4
blank rows like this:

First and Last Name
123 Any Street
My Town, CA
90224
blank row
blank row
blank row
blank row
First and Last Name
222 Maple Avet
Big City, CA
90238
etc

to columns in a new worksheet that will look like this:

column A B C
D
First and Last Name 123 Any Street My Town, CA 90224
First and Last Name 222 Maple Ave Big City, CA 90238

Can anyone write a macro to accomplish this please?

Thank you in advance!!

Keith

 

[Max]

Assuming data in A1 down,
Put in B1:
=INDEX($A:$A,ROW(A1)*8-8+COLUMN(A1))
Copy B1 across 4 cols to E1, then just fill down* until zeros appear,
signalling exhaustion of data.
*to say E820 (as 6500 data rows div by 8 = 813 rows, thereabouts)

 

[Max]

Required results will appear in cols B to E.

If desired, just select cols B to E, then do an "in-place":
Copy > Paste special > check "Values" > OK
to freeze the values. Then delete col A.

---