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

    Transpose macro (2003)

    Attached is an example of what I am trying to do manually. I would like to transpose what is in column C according to column A which is line order. I would like column C transposed beside each occurrance of number 1 in the column designated "Line Order". I have been transposing manually but I have over 5000 to do. Any help would be greatly appreciated. I have a transpose macro that I got here that works off of the group number field but it won't work in this case. The group number is not unique so I have to use the line order column as to what and where to transpose. Here is the macro that I have been using but doesn't work in this scenario:
    Public Sub TransposeRows2()
    Dim oLastCell As Range, oFirstCell As Range
    With Worksheets("Sheet1")
    Set oLastCell = .Range("N65536").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).Entir eRow.Delete
    End With
    End Sub
    Attached Files Attached Files

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

    Re: Transpose macro (2003)

    Try this. It outputs the transformed data in a new sheet.

    Sub TransposeRows()
    Dim s As Long
    Dim t As Long
    Dim c As Long
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    ' Current sheet contains source data
    Set wshS = ActiveSheet
    ' New sheet for output
    Set wshT = Worksheets.Add
    ' Loop through source rows
    For s = 2 To wshS.Cells(65536, 1).End(xlUp).Row
    If Not wshS.Cells(s, 2) = wshS.Cells(s - 1, 2) Then
    ' Start new row
    c = 1
    t = t + 1
    wshT.Cells(t, c) = wshS.Cells(s, 2)
    End If
    ' Write value
    c = c + 1
    wshT.Cells(t, c) = wshS.Cells(s, 3)
    Next s
    ' Resize columns
    wshT.Columns.AutoFit
    End Sub

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

    Re: Transpose macro (2003)

    I don't think that will work. The example I posted was just the tail end of a lot more columns. I just need to be adjust the macro to incorporate where the column is in my real report.

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

    Re: Transpose macro (2003)

    You'll have to adjust the start numbers in the code. Without knowing what your real data look like, I cannot do that for you.

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

    Re: Transpose macro (2003)

    Where in the code would I make the adjustment. I use columns A to AM in the real report.

    I tried the code on the example data but it doesn't do what it should. It is using the grpnum column to decide where to break but it should put the words in column C according to when there is a 1 in line order column. There should be duplicate grpnums in some cases.

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

    Re: Transpose macro (2003)

    I thought I understood what you wanted, but apparently I'm mistaken. Could you post a workbook with what you want the result of the macro to be when it is applied to the sample workbook you posted in this thread?

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

    Re: Transpose macro (2003)

    I have attached the example with another tab showing the way the data should be transposed.
    Attached Files Attached Files

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

    Re: Transpose macro (2003)

    Thanks. Here is a new version:

    Sub TransposeRows()
    Dim s As Long
    Dim t As Long
    Dim c As Long
    ' Loop through source rows
    For s = 2 To Cells(65536, 1).End(xlUp).Row
    If Cells(s, 1) = 1 Then
    ' New series
    c = 3
    t = s
    Else
    ' Move value
    c = c + 1
    Cells(s, 2).ClearContents
    Cells(s, 3).Cut Destination:=Cells(t, c)
    End If
    Next s
    ' Resize columns
    Columns.AutoFit
    End Sub

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

    Re: Transpose macro (2003)

    It works perfect on the example data. The columns in my real data that have the 3 columns as the example data are AK, AL, AM. These columns are at the very end.

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

    Re: Transpose macro (2003)

    Here is a slightly modified version:

    Sub TransposeRows()
    ' Column AK is number 37
    Const StartCol = 37
    Dim s As Long
    Dim t As Long
    Dim c As Long
    ' Loop through source rows
    For s = 2 To Cells(65536, StartCol).End(xlUp).Row
    If Cells(s, StartCol) = 1 Then
    ' New series
    c = StartCol + 2
    t = s
    Else
    ' Move value
    c = c + 1
    Cells(s, StartCol + 1).ClearContents
    Cells(s, StartCol + 2).Cut Destination:=Cells(t, c)
    End If
    Next s
    ' Resize columns
    Columns.AutoFit
    End Sub

    The constant StartCol = 37 at the start corresponds to column AK. If your data had been in columns F, G and H you would have used StartCol = 6, etc.

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

    Re: Transpose macro (2003)

    Thank you so much Hans. I ran the macro on the data and it ran in seconds. Doing it manually, would of taken days. One day of doing it manually, I only did 500 rows and I have over 5000. Thank you, thank you.

Posting Permissions

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