Custom Sort in a Macro

[kpgarber]

Hi,

I am building a macro that involves a custom sort. I do not know what the last row will be, so I am using a Range formula to calculate it. When I run the macro, it processes just fine, but does not perform the sort... can someone please help. Here is my macro:

Sub CommU()'
' CommU Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    Rows("2:2").Select
    Selection.Delete
    
    R = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Cells.Select
    Application.AddCustomList ListArray:=Array("MACHINE", "MANUAL", "P B S")
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "C2:C" & R), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "G2:G" & R), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "MACHINE,MANUAL,P B S", DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "F2:F" & R), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:BG" & R)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With




    
    Cells.Select
    Selection.Replace What:="0", Replacement:=" ", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Selection.End(xlUp).Select
    Range("BA1").Select
    ActiveCell.FormulaR1C1 = "MCC #151197"
    Cells.Select
    Range("BB1").Activate
    Cells.EntireColumn.AutoFit
    Selection.End(xlUp).Select
    Columns("C:C").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF(C$1:C1,C1)=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("A1:BA1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .Bold = True
    End With


    
    
    Columns("M:O").Select
    Selection.EntireColumn.Hidden = True
    Columns("Q:R").Select
    Selection.EntireColumn.Hidden = True
    Columns("Z:Z").Select
    Selection.EntireColumn.Hidden = True
    Columns("Y:Y").Select
    Selection.EntireColumn.Hidden = True
    Columns("V:V").Select
    Selection.EntireColumn.Hidden = True
    Columns("W:W").Select
    Selection.EntireColumn.Hidden = True
    Columns("AB:AB").Select
    Selection.EntireColumn.Hidden = True
    Columns("AE:AE").Select
    Selection.EntireColumn.Hidden = True
    Columns("AG:AY").Select
    Selection.EntireColumn.Hidden = True
    Columns("BA:BA").Select
    Selection.EntireColumn.Hidden = True
   
    Range("A1").Select
    ChDir "Y:\Client Service\Clients\Amex\Production\Reporting"
    Range("a1").Select
    Selection.AutoFilter
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    
  


  
  
End Sub

 

[Asala42]

I usually just take advantage of "CurrentRegion". For example something similar to:

 ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C2").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[kpgarber]

Thank you, but I was unable to get this working either...

I usually just take advantage of "CurrentRegion". For example something similar to:

 ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C2").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With