Layla

If the data is consistent......4 rows per set.....try this macro.

Sub ColtoRows_NoError()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Application.ScreenUpdating = False
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

On Fri, 18 Feb 2005 10:23:44 -0500, "Lady Layla" wrote:

I have an employee who has saved a mailing list as the actual address labels
within Word. We have pulled this listing over to Excel wanting to change it
from the current layout to a more practical database style listing

The format is currently

Name
Company
Address
City, State Zip

We would like to transpose each "label" into the following format without having
to physically cut an paste.

Name Company Address City State Zip

Temporary solutions we have started trying is to use the Array Forumla
Transpose. This at leasts pulls 1 label into a row, but is there a macro or
other way to do this more efficiently?

I know I have seen something similar here but can not find it.


Thanks!