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
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
Thank you for your help
[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]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thank you xld , problem solved in less than 5 minutes.
I'm torn between gratitude and depression.
Thank you for your help