Excel

September 15, 2008 2:40 PM

Using VBA in Excel 2007, how could I place the contents of Row 1 from left to right into Column 1 going top to bottom?

And without knowing how many cells will be filled before I run the macro? (Sometimes there may be 10 cells filled in row 1, other times 15, etc.)

posted by londontomasbird to computers & internet (4 comments total)

Try recording a macro where you start in the leftmost cell of the row; control-shift-right arrow to select everything in the row; edit>copy; move to the top of the column where you want to paste; edit>paste special>transpose.

this won't work perfectly if you want to pivot around cell A1... in that case you might try starting in B1 and pasting to A2...
posted by Perplexity at 3:08 PM on September 15, 2008

Can't test it as I'm on my linux machine at the moment, but something like...



Sub BestVersion()
' Jack in the UK
' www.excel-it.com

'
Dim rSource As Excel.Range
Dim rDestination As Excel.Range
Set rSource = Range("A1").End(xlToRight).Select
Set rDestination = ActiveSheet.Range("a1")

rSource.Cut
rDestination.Select

Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True

Range("A1").Select

Application.CutCopyMode = False

valKill:
Set rSource = Nothing
Set rDestination = Nothing

Exit Sub


(from http://www.ozgrid.com/forum/showthread.php?p=111223)
posted by roofone at 3:09 PM on September 15, 2008

The first one works. The second one (the macro by roofone) has an error in the line:

set rSource = Range("A1").End(xlToRight).Select

The debugger says an object is required. Not sure how to fix that. Any thoughts?
posted by londontomasbird at 3:29 PM on September 15, 2008

try

Set rSource = ActiveSheet.Range("A1").End(xlToRight).Select

I think the object missing is a worksheet.
posted by roofone at 3:35 PM on September 15, 2008