I have a situation where i need to transpose rows to columns, if the columns are blank i need to skip them the header of the column has a value, i want that to be transposed. My attached excel has a macro i was able to build but was unsuccessful in getting the corresponding header row.
appreciate your help in advance
sri
Re: Transpose columns to rows via macros/vba
I found a lot of examples here in the forum, my situation seems a little different how do i copy my correspoding header row, i'm not able to achieve this or i'm still not finding the correct formula to do this
Re: Transpose columns to rows via macros/vba
Srisansan
Does this achieve what you want?
Sub TRtoCol()
Dim a, b(), i As Long, j As Long, k As Long, x As Long
a = Sheets("Sheet1").Cells(1).CurrentRegion.Value
ReDim b(1 To UBound(a, 1) + (UBound(a, 1) * UBound(a, 2)), 1 To UBound(a, 2))
For i = 2 To UBound(a, 1)
For j = 6 To UBound(a, 2)
If a(i, j) <> "" Then
x = x + 1
For k = 1 To 4
b(x, k) = a(i, k)
Next
b(x, 6) = a(1, j): b(x, 7) = a(i, j)
End If
Next
Next
Sheets("Sheet3").Cells(1).Resize(UBound(b, 1), UBound(b, 2)).Value = b
End SubRe: Transpose columns to rows via macros/vba
Thanks a ton Dangle, this was awesome and helped me to achieve what i was trying. I have one question is there any limitation when we use arrays to process these? does my system memory also play a part, why i'm asking this because i have 1000 odd rows and 65 columns.
I can always split the files into multiple and run the same transpose again multiple times
Thanks once again, appreciate your Help
Sri
Re: Transpose columns to rows via macros/vba
You should be fine with that amount of data.
See MS article:
http://support.microsoft.com/d…aspx?scid=kb;en-us;177991
I've started getting problems when writing much more than 6 x 1048576 back to Excel but others may have different experiences.
Re: Transpose columns to rows via macros/vba
Thanks once again, will try it and update you
Don’t have an account yet? Register yourself now and be a part of our community!