Changing layout of a list.

[Shadoxic]

Ok, basically i have a 27 thousand row spreadsheet as follows all the way down:
A B C D E F
1 Name __________________Address 1 ___________________Address 2 _______________Address 3 _____________________Address 4 ____________________Address 5
2
3 Mr John Smith
4 12, cottage road
5 Kelvindale
6 Glasgow
7 Scotland
8 G61 1234
9
10 Miss Swanson
11 13, Glendarule Sq.
12 Coatbridge
13 Glasgow
14 G61 1231
15

I need to somehow change the layout to the following:

A B C D E F
1 Name ______________Address 1 _____________Address 2 ___________Address 3 _______________Address 4 _____________Address 5
2
3 Mr John Smith _____12, cottage road _________Kelvindale ___________Glasgow ________________Scotland ______________G61 1234
4
5 Miss Swanson _____13, Glendarule Sq. _______Coatbridge ____________Glasgow ______________________________________G61 1231

I have literally not the slightest whim as to where to begin, so any help whatsoever would be greatly appreciated. Also, if I have failed to specify any info, I apologise and will answer any questions.

Thank you!

EDIT: The underscores represent between two cells

 

[Ruddles]

Someone asked a very similar question yesterday: http://www.mrexcel.com/forum/showthread.php?p=2767489.

Make sure your addresses are in column A, no row headings please, and try that code.

 

[VoG]

Try this with a copy of your sheet

Sub Addrs()
Dim Area As Range
Application.ScreenUpdating = False
For Each Area In Columns("A").SpecialCells(xlCellTypeConstants).Areas
    Area(1).Offset(, 1).Resize(, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
Columns("A").Delete
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

 

[Shadoxic]

Try this with a copy of your sheet

It worked! My word, thank you very much