Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transpose column (2000)

    I have data (see attached). I am taking column B and copying, paste special and Transposing to get the numbers in column B to go in one row next to the number that is in column A. Once I transpose the numbers in column B, I delete column B. This would be fine if I had a few to do but my spreadsheet contains over 1000. Is there a macro that can do this. Thanks for any help you can provide....

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column (2000)

    You can do multiple columns in one go.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column (2000)

    It really isn't the column. I only go down the column as far as the next number that is in column A and copy, paste special and transpose. I have over a 1000 numbers that are in column A. How can you do multiple columns in one go?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column (2000)

    I couldn't discern exactly what you need from the attachment. Could you explain (in plain text) a bit more?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column (2000)

    I have client numbers in column A. In column B, I have the corresponding group numbers. One Client can have many group numbers. Instead of each clients group numbers going down, I need to take the clients group numbers and put then going across. Every time the client number in row A changes, I take the group numbers and copy, paste special, transpose so I have the client number and the group numbers in the same row. I hope this is clear.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column (2000)

    The following macro should do that. If you don't want column B deleted after the transpose, then remove the third from last line in the macro.

    <pre>Public Sub TransposeRows()
    Dim oLastCell As Range, oFirstCell As Range
    With Worksheets("Sheet1")
    Set oLastCell = .Range("B65536").End(xlUp)
    Set oFirstCell = oLastCell.Offset(0, -1).End(xlUp).Offset(0, 1)
    Do While Not oFirstCell Is Nothing
    Range(oFirstCell, oLastCell).Copy
    oFirstCell.Offset(0, 1).PasteSpecial Transpose:=True
    If oFirstCell.Row = 1 Then
    Set oFirstCell = Nothing
    Set oLastCell = Nothing
    Else
    Set oLastCell = oFirstCell.Offset(-1, 0)
    Set oFirstCell = oLastCell.Offset(0, -1).End(xlUp).Offset(0, 1)
    End If
    Loop
    Application.CutCopyMode = False
    .Range("B:B").Delete
    End With
    End Sub
    </pre>

    Legare Coleman

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column (2000)

    Perfect - Thank you so much. This saves me a lot of time. This is for a mailing so that these group numbers can be inserted into a letter for each client.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column (2000)

    This macro works great when the client has multiple groups but when the client only has one group, it takes that one group and tacks it on to the end of the previous client's groups.
    To get around this, I manually inserted a blank row after the clients that had one group and then ran the macro without the code to delete column B.
    That worked fine and there wasn't too many that had only one group but this is a great macro and I would like to use it again.
    Can you revise it so that clients with only one group transpose correctly? If not, it is still a great macro.
    Thanks for your help.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column (2000)

    Does this do what you want? I also added a line to delete the empty rows after transposing (Thanks Steve).

    <pre>Option Explicit

    Public Sub TransposeRows()
    Dim oLastCell As Range, oFirstCell As Range
    With Worksheets("Sheet1")
    Set oLastCell = .Range("B65536").End(xlUp)
    If oLastCell.Offset(0, -1).Value <> "" Then
    Set oFirstCell = oLastCell
    Else
    Set oFirstCell = oLastCell.Offset(0, -1).End(xlUp).Offset(0, 1)
    End If
    Do While Not oFirstCell Is Nothing
    Range(oFirstCell, oLastCell).Copy
    oFirstCell.Offset(0, 1).PasteSpecial Transpose:=True
    If oFirstCell.Row = 1 Then
    Set oFirstCell = Nothing
    Set oLastCell = Nothing
    Else
    Set oLastCell = oFirstCell.Offset(-1, 0)
    If oLastCell.Offset(0, -1).Value <> "" Then
    Set oFirstCell = oLastCell
    Else
    Set oFirstCell = oLastCell.Offset(0, -1).End(xlUp).Offset(0, 1)
    End If
    End If
    Loop
    Application.CutCopyMode = False
    .Range("B:B").Delete
    .Range("A:A").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete
    End With
    End Sub
    </pre>

    Legare Coleman

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transpose column (2000)

    Perfect - thanks so much. I am sure I will be using this macro again.

Posting Permissions

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