Macro to reformat spreadsheet?

**hjchristman** · 02-08-2010, 06:44 PM

I am importing data into a spreadsheet. Each record has 6 to 11 fields. The data gets imported as one long list...so all date is in colum A. The first record in cells a1 to a11, then a space, then the second in a12 to a20, space...and so on. I want to transpose the records so the data goes across the colums (A through K). I know I can transpose it on record at a time using the Paste->transpose data command...but there are many records and this will take days. How can I create a macro to transpose the entire worksheet at once??

**JBeaucaire** · 02-08-2010, 07:46 PM

Try this on a copy of your data, it will only copy once per area...

Option Explicit

Sub ReformatData()
'JBeaucaire  2/8/2010
'Reorganize groups of data in column A into row format
Dim i As Long, RNG As Range
Application.ScreenUpdating = False

Set RNG = Columns("A:A").SpecialCells(xlCellTypeConstants)

For i = 1 To RNG.Areas.Count
    RNG.Areas(i).Copy
    Range("B" & i).PasteSpecial xlPasteAll, Transpose:=True
Next i

Columns(1).Delete xlShiftToLeft
Application.ScreenUpdating = True
End Sub

It blew through 5500 rows of data, 650 "groups" in about 3 seconds.