Solved: Converting rows to columns

[VJ1234]

Hi,
I have a row of data in Excel. The number of columns may vary on a case by case basis. I wish to transform the data from 1 row to 1 column (Like the first row has to be the first column). I tried recording a macro using the transpose option under paste special. Found 2 reasons which limit this approach.
1) Paste special is not working if I cut the data. It works only if I do a copy.
2) Less flexible in handling the variations in the number of columns.

Is there any other way of achieving this by an excel macro or VBA? Thanks in advance for your help!

[mbarron]

1)Why not copy / paste special / delete the original cells.

If you had uploaded a sample, a more exact solution is possible. This exapmle takes A1 and its region of cells and does a transpose paste on the following sheet - sheet2 if the macro is run on sheet1 etc. It then removes the region from the spreadsheet.


Sub transPaste()
Dim myRange As Range, shtNew As Worksheet, shtCur As Worksheet
Set shtCur = ActiveSheet
Set shtNew = Worksheets(shtCur.Index + 1)
Set myRange = shtCur.Range("a1").CurrentRegion

myRange.Copy
shtNew.Range("a1").PasteSpecial xlPasteAll, xlNone, False, True
myRange.ClearContents
End Sub
I think this version is more what you are after though (after I re-read your post).

Sub transPaste2()
Dim myRange As Range
Set myRange = Range("a1").CurrentRegion
myRange.Copy
Range("a2").PasteSpecial xlPasteAll, xlNone, False, True
myRange.Delete Shift:=xlUp
Range("A1").Select
End Sub

[VJ1234]

Excellent!! Option 2 is inline to my expectations. Thanks a lot for your help!