Sort a column in Excel 2007



I have a worksheet with over 2000 rows of information but it's all
symmetrical. Meaning, Row 1 is a Company Name. Row 2 is the Street Address.
Row 3 is the City, State, Zip. Row 4 is the Phone Number. Row 5 is the Fax
Number. Then Row 6 starts the next Company Information in the same order. I
want to sort this information into Columns so that Column 1 is Company Name,
Column 2 is Street Address, etc.

Does anyone know how this can be done? Thank you.




Thanks Adnan,

Since the column is over 2000 rows I was hoping for an operation to
transpose everything at once. Any ideas?



Dave Peterson

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowManyPerGroup As Long
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

HowManyPerGroup = 5

With wks
FirstRow = 1 ' no headers
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow Step HowManyPerGroup
.Cells(iRow, "A").Resize(HowManyPerGroup, 1).Copy
.Cells(iRow, "B").PasteSpecial Transpose:=True
Next iRow

'clean up original data

'clean up empty rows
On Error Resume Next
On Error GoTo 0

End With

End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question