Solved: Special Transpose

[ndendrinos]

I import data from Access to Excel and end up with many rows that I need to "transpose" in a specific way to do a search.
My example show in the first three rows how the data is imported and staring in A6 how I need to transpose it.
thank you

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 1 Step -1

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
If LastCol > 2 Then

.Rows(i + 1).Resize(LastCol - 2).Insert
.Cells(i, "C").Resize(, LastCol - 2).Copy
.Cells(i + 1, "B").PasteSpecial Paste:=xlPasteAll, Transpose:=True
.Cells(i, "C").Resize(, LastCol - 2).ClearContents
End If
Next i
End With

With Application

.CutCopyMode = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[ndendrinos]

Thank you xld , problem solved in less than 5 minutes.
I'm torn between gratitude and depression.