Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trasnpose Range of cells (VBA Excel 2003)

    I need to transpose several rows of cells in to on long collom. So I have

    123 321 456 231
    546 621 313 213

    I need to take the 1st row and copy it to a second worksheet as a colom, no problem. Then I need to take the second row and past it below the 1st , also trasposed. I need to do this to 285 5 cell rows! I am very new to VBA but here is what I have that does not work, it's intresting what it does though.

    Sheets("DATA").Select
    Dim r As Integer
    Dim finalrow As Integer
    finalrow = Range("a1").End(xlDown).Row
    r = 1
    Do Until r = finalrow + 1
    Sheets("DATA").Select
    Range("a1").Select
    Range(Cells(r, 1), Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("New").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    ActiveCell.Offset(5, 0).Select
    r = r + 1
    Loop

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trasnpose Range of cells (VBA Excel 2003)

    Welcome to Woody's Lounge!

    Does this do what you want?

    Sub Test()
    Dim r As Long
    Dim oCell As Range
    For Each oCell In Worksheets("Data").Range("A1").CurrentRegion
    r = r + 1
    Worksheets("New").Range("A" & r) = oCell.Value
    Next oCell
    End Sub

  3. #3
    New Lounger
    Join Date
    Oct 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trasnpose Range of cells (VBA Excel 2003)

    Yes it does. I knew it was going to be simple to do. Now I get to figure out what each line does so I can use it again.

    Thanks again!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Trasnpose Range of cells (VBA Excel 2003)

    Feel free to post back if you have questions about the macro.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •