Excel Copy, Paste, Transpose below loop until

Question

I am a total beginner at VBA and couldn't find or piece together the code to do what I want to do. It seems like it should be simple, I am just so unfamiliar with VBA right now I am having trouble.

here is what I am trying to do in a loop until row 1 is empty

Here is an image of what I have so far

I know i can delete the lines after "False, Transpose:=True"

I am not sure how to add the loop or the do until Row 1 is empty and to keep moving the transposed paste down the row.

Thanks in advance!

Instead of giving a picture of the code, can you kindly edit it in to your post, and format with the code tags, {}? Thanks! — BruceWayne
– BruceWayne, Commented Jul 17, 2017 at 20:21

Scott Craner · Accepted Answer · 2017-07-17 20:30:54Z

2

This code:

Turns this:

Into this:

answered Jul 17, 2017 at 20:30

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

1 Comment

Ron Rosenfeld Over a year ago

I like the screenshots!

Andrew Map · Accepted Answer · 2017-07-17 21:06:31Z

1

Well actually you have two solutions :

 Range("A1:C5").Select
 Selection.Copy
 Range("A12").Select
 Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
 False, Transpose:=True

Or you can make it more flexible by doing

Sub transposeTable() 
Dim intLine, intCol, intLine2, intCol2 as Integer

    intLine = Sheets(SHEET_PAGE).range(strRange).Row 
'Ex : Sheets("Sheet1").range("firstTable")

    intCol = Sheets(SHEET_PAGE).range(strRange).Column
    intLine2 = Sheets(SHEET_PAGE).range(strRange2).Row
    intCol2 = Sheets(SHEET_PAGE).range(strRange2).Column

    While Sheets(SHEET_PAGE).Cells(intLine, intCol) <> ""
         ' Transpose code here, where the value at a(i,j) goes to a(j,i)       
        intLine = intLine + 1
    Wend

End Sub

answered Jul 17, 2017 at 21:06

Andrew Map

311 silver badge6 bronze badges

3 Comments

Ron Rosenfeld Over a year ago

What is your reason for using Select and Selection in your first method? Why not just use Range("A1:C5").Copy and Range("A12").PasteSpecial ...(various arguments)...?

Andrew Map Over a year ago

@RonRosenfeld Just because it seems more organized in that way, so I can see clearly which range is being selected.

Ron Rosenfeld Over a year ago

I suggest you read How to avoid using select in excel vba macros, especially the response of @SiddharthRout who provides the reasoning as to why this should be avoided, and others who show a better way to organize your code.

Collectives™ on Stack Overflow

Excel Copy, Paste, Transpose below loop until

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related