PDA

View Full Version : Solved: Insert Rows and Transpose



Minstrel
09-27-2007, 01:35 AM
I have a spreadsheet with 3000+ rows of data.
I need to be able to change the layout from horizontal to vertical
In the first column, I have a category. The next 6 columns are months (Jan 2007 to June 2007).
Each category has a value in one or more months.
I want to show this as 2 columns: Category in the first column and Monthly Spend in the next. So there would be 6 rows for each Category rather than 6 columns.
I've tried using Transpose, but with so much data it's taking a loooooong time. :(
Can anyone suggest code solution? :think:

Thanks in advance

Bob Phillips
09-27-2007, 01:49 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

Application.ScreenUpdating = False

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1
.Rows(i + 1).Resize(5).Insert
.Cells(i + 1, "A").Resize(5).Value = .Cells(i, "A").Value
.Cells(i, "C").Resize(, 5).Copy
.Cells(i + 1, "B").Resize(5).PasteSpecial Paste:=xlPasteAll, Transpose:=True
.Cells(i, "C").Resize(, 5).ClearContents
Next i

Application.ScreenUpdating = True
End With

End Sub

Minstrel
09-27-2007, 02:12 AM
Many many thanks xld :clap:
The code does just what I want :thumb

Tom :beerchug: