1

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!

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
qdev
  • 11
  • 1
  • 3
    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 Jul 17 '17 at 20:21

2 Answers2

2

This code:

enter image description here

Turns this:

enter image description here

Into this:

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
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
Andrew Map
  • 31
  • 1
  • 6
  • 1
    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)...`? – Ron Rosenfeld Jul 17 '17 at 21:56
  • @RonRosenfeld Just because it seems more organized in that way, so I can see clearly which range is being selected. – Andrew Map Jul 18 '17 at 01:49
  • 1
    I suggest you read [How to avoid using select in excel vba macros](https://stackoverflow.com/questions/10714251/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. – Ron Rosenfeld Jul 18 '17 at 02:21