Eu respondi isso alguns dias atrás, mas minha resposta parece ter sido perdida. Talvez eu não tenha postado corretamente desde que sou novo neste site.
Não tenho vivido nos Estados Unidos desde o final dos anos 1970, mas nossos dois endereços em Detroit tinham números diferentes de linhas. A solução de fórmula depende de cada endereço ter o mesmo número de linhas, portanto, não vejo isso como uma solução adequada.
Eu coloquei os seguintes endereços ingleses em Sheet1. O primeiro é um endereço típico do Reino Unido. Os outros dois são reais (exceto pelo nome e código postal), mas menos comuns. O segundo é um endereço em estilo de vila com casas nomeadas em vez de numeradas e a cidade local incluída além do nome da vila. O terceiro endereço é mais incomum, pois não tem nome de rua. Como o endereço sugere, a casa foi construída nas ruínas do que antes era a fachada oeste da abadia de Bury St Edmunds.
John Smith
5 Acacia Avenue
York
Yorkshire
YO3 2RG
Sarah Jones
Lilac Cottage
Chapel Lane
Houghton
Huntingdon
Cambridgeshire
CB12 4TG
Alice Green
1 The West Front
Abbey Ruins
Bury St Edmunds
IP33 1RS
A macro a seguir copia esses endereços para Sheet2 no estilo que você procura.
Sub Test2()
Dim Col1Crnt As Integer
Dim Col1Max As Integer
Dim Col2Crnt As Integer
Dim Sheet1() As Variant
Dim Row1Crnt As Integer
Dim Row1Max As Integer
Dim Row2Crnt As Integer
With Sheets("Sheet1")
' Find the last used row in Sheet1
Row1Max = .Cells.Find("*", .Range("A1"), xlFormulas, _
, xlByRows, xlPrevious).Row
' Load column 1 to Sheet1.
Sheet1 = .Range(.Cells(1, 1), .Cells(Row1Max, 1)).Value
' Although I am only loading one column, Sheet 1 will be a 2D array
' with the row as the first dimension and the column as the second.
' I have loaded Sheet1 to an array because switching between
' worksheets is very slow.
End With
With Sheets("Sheet2")
Row2Crnt = 1
Col2Crnt = 1
For Row1Crnt = 1 To Row1Max
If Sheet1(Row1Crnt, 1) = "" Then
' blank line
If Col2Crnt <> 1 Then
' Only advance row for first blank row
Row2Crnt = Row2Crnt + 1
Col2Crnt = 1
End If
Else
.Cells(Row2Crnt, Col2Crnt).Value = Sheet1(Row1Crnt, 1)
Col2Crnt = Col2Crnt + 1
End If
Next
End With
End Sub
Espero que isso ajude