Selecting cells between blanks

[rickbanny]

Hi Guys - hope you can help as I've been pulling my hair out.

I've got a list of names + addresses separated by an empty cell all in column A e.g

A1 - Mr B Bloggs
A2 - 1 High Street
A3 - Lowtown
A4 - LT1 1HJ
A5 -
A6 -Mrs J Jones
A7 -10 Upper Road
A8 -Upsville
A9 - UP1 5TG

... and so on, about 1000+ addresses some over 2 rows, some over 5.

What I've been (unsuccessfully) trying is to loop through column A pick up a name and address and Transpose paste it starting in column B then repeat. Once complete, delete column A so I'm left with normalised data.
I've got the pasting bit nailed down, it's just the selecting, copying and looping bit that I'm struggling with.

Any help would be greatly appreciated.

Cheers

[daffodil11]

Could probably do it better with Visual Basic coding, but this should get you off in the right direction.

First, insert a row at the top to create a blank value in A1.

Then, in B2 paste:

=IF(AND($A1="",$A2<>"",A2<>"",INDIRECT("$A"&COLUMN(C1)+(ROWS($A$1:A1)-1))<>0),INDIRECT("$A"&COLUMN(C1)+(ROWS($A$1:A1)-1)),"")

And copy that out across as many columns and down as many rows as needed.

Edit: Missed the rows offset. All fixed.

[rickbanny]

Really sorry - I should have said I was using VBA to try and achieve this - my bad.

[daffodil11]

I'd recommend messaging a moderator to request having your post moved over the the VB Forum where the code monkeys play.

At the very least, I'm sure the above logic could be converted into VB.

My code is pretty bad and I'd probably do it using For Each Cell, a Loop, a lot of Application.Function and closing with setting cell values to value to kill the formulas. I tend to accomplish in 75-100 lines what most people can do in 10-15.



tranpositions.xlsx

[rickbanny]

Thanks for trying - really appreciated.

[arlu1201]

Thread moved to the VBA / Programming subforum.

[jolivanes]

Are these all blocks of 4, space, block of 4, space etc etc?

[adyteo]

I have 5 columns: Full Details (your column A), Name, Street, City, Postal Code.
I assumed there are blocks of 4 plus one empty row.
this is the macro:

Please Login or Register  to view this content.

[jolivanes]

Or this (If they are blocks of 4, you could try this on a copy of your workbook.)

Please Login or Register  to view this content.

[jindon]

Perhaps

Please Login or Register  to view this content.

[rickbanny]

Hi

No - There can be a block of 4 then a block of 5 then 2 and so on.

[rickbanny]

Perhaps

Please Login or Register  to view this content.

jindon - you are an absolute star, been struggling with this for a while

Cheers

[adyteo]

Then how do you know if, for example, on row 2 of the group is the Postal Code or the Street name?

[rickbanny]

Then how do you know if, for example, on row 2 of the group is the Postal Code or the Street name?

I don't - the column heading will be simply Add_Line1, Add_line2 and so on. Word's Mail Merge will deal with any blank entries in the worksheet.

[jindon]

Can you mark this thread as "Solved", if you are happy with the solution provided?