vba rows to columns for addresses

[Cathmac801]

Hi all

I have addresses in col A as follows

Person 1
Address line 1
Address line 2
City
Country
BLANK ROW
Person 2
Address line 1
Address line 2
Addres line 3
City
Country
BLANK ROW

etc

How do I get them to show up as
Person 1 Address line1 Address line 2 City Country
Person 2 Address line 1 Address line 2 Address line 3 City Country

Each person has numerous lines but each complete address has a blank row between it and the next address

thanks a mil
Cath

 

[Cathmac801]

its ok I have found the answer http://www.mrexcel.com/forum/excel-...cations-macro-copy-transpose-data-column.html

Apologies I should have searched harder.

 

[mancubus]

hey.
i've been working on this.

writes concatenated values of cells to column C in the same sheet.

Sub mrxl_854289_multiCell_to_singleCell()

    Dim ar As Range
    Dim i As Long
    
    With Worksheets("Sheet1") 'change worksheet name to suit
        For Each ar In .Columns(1).SpecialCells(xlCellTypeConstants, 23).Areas
            i = i + 1
            .Range("C" & i) = Join(Application.Transpose(Range(ar.Address).Value), " ")
        Next
    End With

End Sub