Transpose from rows to column (VBA)

Question

My question is: how to transpose the last four rows of the column G into different columns?

I usually use this static code:

Worksheets("Sheet8").Range("A1:A5").Copy
Worksheets("Sheet9").Range("A1").PasteSpecial Transpose:=True

But it doesn't allow me to stay on the same Sheet.

So I am trying to combine it with this code:

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

Before

After

Any help is appreciated

Your question is a bit confusing: are you working on ColA or ColG? — Tim Williams
– Tim Williams, Commented Jul 21, 2016 at 16:22
Sorry, it is confusing indeed. I am using ColG - ColA was a snippet of another code — pdx
– pdx, Commented Jul 21, 2016 at 16:23

Tim Williams · Accepted Answer · 2016-07-21 16:40:20Z

1

Untested:

Dim c As Range, v

'find last-used cell in ColG
Set c = Cells(Rows.Count, "G").End(xlUp)

With c.offset(-3,0) 'starting with the cell 3 rows above the last-used cell...
    v = .resize(4,1).value      'get the value of the 4-row/1-col range below
    .resize(4,1).clearcontents  '...then clear that range
    .resize(1,4).value = Application.Transpose(v) 'place the values in a row
End with

edited Jul 21, 2016 at 16:40

answered Jul 21, 2016 at 16:26

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pdx Over a year ago

Worked really well, thanks! Grateful if you could write down a quick explanation of your code!

Collectives™ on Stack Overflow

Transpose from rows to column (VBA)

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related