VB Code to Convert Rows into Columns in Excel

[Adel Pascaris]

Hi all,

I am trying to write a VB module in Excel that converts rows into columns.
The data is distributed across 10-50 rows instead, I'd like to see 10-50
columns.
This is the code that I used but doesn't seem to be working for me:

i = Range("A50000").End(xlUp).Row
Do
If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
Cells(i, 2).Resize(1, 50).Cut Cells(i - 1, 3)
Rows(i).Delete
End If
i = i - 1
Loop Until i < 2
Columns.AutoFit

Thanks in advance,

Adel

 

[Joel]

You can use transpose, but you have to past the data to a new workarea and
then copy it back to thje original worksheet. code assumes Column A and Row
1 defines the area to transpose (column A has data in last row, and row 1 has
data in last column).

LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set Transposerange = Range("A1", Cells(LastRow, LastCol))
Transposerange.Copy
Sheets("sheet2").Range("A1").PasteSpecial Paste:=xlPasteAll, Transpose:=True

 

[Adel Pascaris]

Thanks Joel, I tried your suggestion, but it doesn't seem to be working for
me. I am working with multiple spreadsheets that have a formatting issue,
therefore I wanted to automate the process of converting rows into columns
by using some VB coding, but the code doesn't seem to be working.