VBA: transpose last row

Question

I am trying to transpose the last row of my spreadsheet so that it becomes a column.

I have started such that I retrieve the last column/row number of the spreadsheet but I don't know how to make it an array to transpose it with Application.transpose(myarray) or Application.WorksheetFunction.Transpose(myarray)

Dim lRow As Long, lCol As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Any tips?

Thanks

From what I can tell, this is what you are looking for. http://stackoverflow.com/questions/13174916/transpose-a-range-in-vba — Barry Thomas, Jul 12 '16 at 14:27

score 1 · Accepted Answer · answered Jul 12 '16 at 14:32

1

Starting with:

Running:

Sub xPose()
    Dim r As Range, N As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    Set r = Cells(N, 1).EntireRow

    r.Copy
    Cells(N + 1, 1).PasteSpecial Transpose:=True
    r.Delete
End Sub

will produce:

answered Jul 12 '16 at 14:32

Gary's Student

95,722
10
59
99

This is EXACTLY what I needed and it works like a charm - I am grateful for people like you helping newbies out! – pdx Jul 12 '16 at 14:35
@pdx We are ALL newbies in some sense. – Gary's Student Jul 12 '16 at 14:36
if you ever have some time... http://stackoverflow.com/questions/38507901/transpose-from-rows-to-column-vba – pdx Jul 21 '16 at 16:02

VBA: transpose last row

1 Answers1