+ Reply to Thread
Results 1 to 3 of 3

Sort Macro

Hybrid View

  1. #1
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Sort Macro

    I recorded this macro to sort a large list in my worksheet, but it takes a few seconds to complete. It's added to other macro's, so if I could speed it up that'd be great. Any ideas?


    Sub SORT()
    '
    ' SORT Macro
    '
    
    '
        Rows("10:414").Activate
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Clear
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(192, 0, 0)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(255, 192, 0)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(255, 255, 0)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(146, 208, 80)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(0, 176, 80)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(0, 176, 240)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(0, 112, 192)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(0, 32, 96)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(112, 48, 160)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(230, 184, 183)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(151, 71, 6)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(49, 134, 155)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add(Range( _
            "AZ10:AZ414"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
            Color = RGB(118, 147, 60)
        ActiveWorkbook.Worksheets("Parts Worksheet").SORT.SortFields.Add Key:=Range( _
            "AZ10:AZ414"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Parts Worksheet").SORT
            .SetRange Range("A10:BJ414")
            .Header = xlGuess
            .MatchCase = True
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("A12").Select
    End Sub

  2. #2
    Registered User
    Join Date
    04-20-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2013, 2010, 2007, 2003
    Posts
    99

    Re: Sort Macro

    What exactly are you trying to achieve- it looks like when you recorded your macro you sorted the list a dozen or so times...
    SPARTAN
    Please click the * if my solution helped

  3. #3
    Registered User
    Join Date
    08-31-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Sort Macro

    I sorted it via color because I have multiple row headers, it now sorts depending on whether the Item number has one of the values I need them sorted by in it. Otherwise it mashed the headers up. I searched for an alternative forever, this is the best way I've found.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1