1

Currently my code only sorts row with containing all "A" in range(E20:I20 till last row) and but I want "A" sorted on top if any row contains "A" within range(E20:I20 till last row). I am stuck with it. Any help is welcome.

Sub SortA()

Range("A19").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Phop").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Phop").Sort.SortFields.Add Key:=Range("E20:E" & Range("E" & Rows.Count).End(xlUp).Row),  SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="A", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Phop").Sort.SortFields.Add Key:=Range("F20:F" & Range("F" & Rows.Count).End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="A", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Phop").Sort.SortFields.Add Key:=Range("G20:G" & Range("G" & Rows.Count).End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="A", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Phop").Sort.SortFields.Add Key:=Range("H20:H" & Range("H" & Rows.Count).End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="A", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Phop").Sort.SortFields.Add Key:=Range("I20:I" & Range("I" & Rows.Count).End(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="A", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Phop").Sort
    .SetRange Range("A19:J" & Range("A" & Rows.Count).End(xlUp).Row)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Range("A20").Select


End Sub
3689
  • 113
  • 10
  • What happens if you drop the `CustomOrder` property? – PaulFrancis Mar 19 '15 at 11:56
  • CustomOrder property is list which I have prepared for sorting it with "A" – 3689 Mar 19 '15 at 12:06
  • You will to refer correctly to the customlist: it's indexnumber . e.g Range("D1:D20").sort range("D1"), Ordercustom:=application.customListcount – snb Mar 19 '15 at 13:41
  • @3689 This looks like it has just been recorded. Please have a look at this [link](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – Tom Mar 19 '15 at 17:02

0 Answers0