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

    Transpose macro (2003)

    I have a column (AL) that contains group numbers. I have another column (AM) that has data that I want to transpose according to the group number field. The group number field is grouped so there will be a group number with 3 lines to be transposed in column AM and then another group number with numerous lines to be transposed in column AM. I am trying to use the following macro but I can't seem to get it to work. I am using the column that has the groups for the range in the macro. There are empty columns beside Column AM.

    Public Sub TransposeRows2()
    Dim oLastCell As Range, oFirstCell As Range
    With Worksheets("Sheet1")
    Set oLastCell = .Range("AL3751").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("N:N").Delete
    '.Range("M:M").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
    End With
    End Sub

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

    Re: Transpose macro (2003)

    Could you post a small sample workbook with dummy data please.

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

    Re: Transpose macro (2003)

    The columns Are AL and AM in the regular spreadsheet.
    Attached Files Attached Files

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

    Re: Transpose macro (2003)

    I tried both AM and AL in the range.

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

    Re: Transpose macro (2003)

    I think the line

    Set oLastCell = .Range("AL3751").End(xlUp)

    should be

    Set oLastCell = .Range("AM3751").End(xlUp)

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

    Re: Transpose macro (2003)

    I did that but it doesn't work. It just puts the Provision Detail heading in column AN. I tested it on the file I sent you and it does the same thing - puts the heading in C.

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

    Re: Transpose macro (2003)

    I have attached your sample workbook with the following changes:
    - I moved the data from columns A and B to columns AL and AM to match your original description.
    - I added the macro with the proposed change.
    - I ran the macro so that you can see the result.
    Attached Files Attached Files

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

    Re: Transpose macro (2003)

    It won't work for me. I added columns to the test file that I sent you and it worked but not when it was in Columns A and B. I tried it on my data, but all it transposes is the title field.

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

    Re: Transpose macro (2003)

    If the columns are A and B, you have to use

    Set oLastCell = .Range("B3751").End(xlUp)

    If you use columns P and Q, you have to use

    Set oLastCell = .Range("Q3751").End(xlUp)

    It does work. Honestly! But you have to edit the macro to use the correct columns.

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

    Re: Transpose macro (2003)

    I do change the column in the macro but for some reason it won't work. I am going to reboot.

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

    Re: Transpose macro (2003)

    I am not sure if this is the problem but every time I open Excel, I get Error 53 File Not Found for Microsoft Visual Basic. I just tried to open an Excel file from an Access database and It gives me that error and it won't open the Excel file.

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

    Re: Transpose macro (2003)

    Try Jan Karel Pieterse's <!post=Systematic Approach to Behavioral Problems in XL,290455>Systematic Approach to Behavioral Problems in XL<!/post>.

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

    Re: Transpose macro (2003)

    I just wanted to let you know that the macro works. My mistake was that I was changing the amount of rows. I should keep the max rows (65536) and just change the column letter. I wrote this in the macro so the next time I use it, I will know to not change the number of rows. Thanks for your help.

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

    Re: Transpose macro (2003)

    Thanks for the update. If you want to make sure that the code works in all versions of Excel, you can use
    <code>
    Set oLastCell = .Range("AM" & .Rows.Count).End(xlUp)
    </code>
    substituting the correct column, of course. (Excel 2007 has more than a million rows instead of the 65,536 of previous versions).

Posting Permissions

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