Solved: Insert Rows and Transpose

[Minstrel]

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?

Thanks in advance

[Bob Phillips]

[vba]

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
[/vba]

[Minstrel]

Many many thanks xld
The code does just what I want

Tom