CODEWITHSUNDEEP
vbaexcelloops
qdev
qdev

Reputation: 11

Excel Copy, Paste, Transpose below loop until

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!

Upvotes: 1

Views: 323

Answers (2)

Andrew Map
Andrew Map

Reputation: 31

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

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152585

This code:

enter image description here

Turns this:

enter image description here

Into this:

enter image description here

Upvotes: 2

Related Questions