Transpose 3 Columns (variable rows numbers) to 3 Rows

[Alternsti]

Hello,

I want to use excel VBA transpose a dynamic matrix (3 columns but row numbers is always variable) but I have a error message 400 with the following.:crying: Can you help/explain please:hi:?


Sub TransposeColsToRows()
With Range("A1", Cells(Rows.Count, 3).End(xlUp)) '3 used for three columns
.Copy
Cells(1, Columns.Count).End(xlToLeft)(1, 1).PasteSpecial Transpose:=True
.Clear
End With
End Sub

[Bob Phillips]

You know your target cell is D1, so just use tha


Sub TransposeColsToRows()
With Range("A1", Cells(Rows.Count, 3).End(xlUp)) '3 used for three columns
.Copy
Range("D1").PasteSpecial Transpose:=True
.Clear
End With
End Sub

[Alternsti]

xld: Good idea but, I'm having error message 1004 (D1 cell is actually selected).

[TheAntiGates]

Your copy has 3 columns times X rows, right? So if there's 6 rows, you copy 18 cells, 6 rows by 3 columns. Then you paste - but to where? First parse what you have as a destination. Start with
Cells(1, Columns.Count)
Tip: you can highlight that and go shift-F9 to observe. To more fully observe, rather than cancelling from that, click "Add" and go nuts browsing the Watch Window.
Anyway so far that's the rightmost possible cell in row 1, cell IV1 in Excel 2003 and prior and something larger in one of those sick newer versions. Now consider
Cells(1, Columns.Count).End(xlToLeft)
This gives the rightmost cell with contents in row one (unless Cells(1, Columns.Count) is occupied). It's what you would get by going control-left arrow from the far right edge of row 1.
Finally the (1,1) is redundant in this case.

Now, whether C1 has contents, or C1 doesn't, and assuming that you have nothing to the right of column C, that .End(xlToLeft) is going to land on either A1, B1 or C1. In each case those are within the original range that you copied from. For a transpose type copy, [I don't believe] you want to paste over the copied area, which you are doing here, and getting a 1004. I say "I don't believe" because every time I ever used transpose, the paste destination was distinct from the copy area. Actually I don't think you can overlap the copy area when you paste with transpose.

http://www.rondebruin.nl/win/s9/win005.htm is a reputable source for understanding End(xlUp) and such; and I also recommend spending time with http://j-walk.com/ss/excel/tips/tip10.htm and everything else at that site. The blog alone may so intrigue you that you never make it back from the site :)

I have very limited time presently to watch this thread but I jumped in to give you things to think about and maybe throw another light on the workings.

[Alternsti]

I finally found a solution (not the best but it works). Thanks for the advise and links.


Public Sub TransposeData()
Range("A1", Range("A" & Rows.Count).End(xlUp)).Copy
Sheets("CellNames").Range("D1").PasteSpecial xlPasteAll, Transpose:=True
Range("B1", Range("B" & Rows.Count).End(xlUp)).Copy
Sheets("CellNames").Range("D2").PasteSpecial xlPasteAll, Transpose:=True
Range("C1", Range("C" & Rows.Count).End(xlUp)).Copy
Sheets("CellNames").Range("D3").PasteSpecial xlPasteAll, Transpose:=True
Application.CutCopyMode = False
End Sub