September 15, 2008 2:40 PM   Subscribe

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 answers 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")


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


Application.CutCopyMode = False

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

Response by poster: 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


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

« Older Let's Do This!   |   Toilet works: slow flushing toilet Newer »
This thread is closed to new comments.