Excel VBA CustomSort从范围

Question

Sub RRC()

Dim noOfLists As String
With Sheets("All_list")
Application.CutCopyMode = False
Application.AddCustomList ListArray:=Range("AU2:AU4")
noOfLists = Application.CustomListCount
noOfLists = noOfLists + 1

End With

ActiveWorkbook.Worksheets("All_list").ListObjects("All").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("All_list").ListObjects("All").Sort.SortFields.Add2 _
        Key:=Range("All[RRC]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        CustomOrder:=CVar(noOfLists), DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("All_list").ListObjects("All").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


Application.DeleteCustomList (noOfLists - 1)


End Sub

任何人都可以帮助弄清楚为什么这不起作用，它运行，但没有排序。
范围 AU2:AU4 将是动态的，这意味着那里的排序总是不同的，因此这里的关键时刻是在应用 VBA 时使用该范围内的最新排序

谢谢

Answer 1

这就是我会使用的。如果您使用 CustomOrder 有什么原因，请告诉我在实际排序中

Sub RRC()

  Dim currWorksheet As Worksheet
  Set currWorksheet = ActiveWorkbook.Worksheets("All_list")
  Dim newRangeSort As Range
  Dim newRangeKey As Range

  ' Fields to be sorted
  Set newRangeSort = currWorksheet.Range("AU2:AU4")
  ' "Header" column of which to sort from
  Set newRangeKey = currWorksheet.Range("AU1")

  'Your sort
  Dim customSort As String
  customSort = ("test")

  'Actual sort
  currWorksheet.Sort.SortFields.Clear
  newRangeSort.Sort Key1:=newRangeKey, Order1:=xlAscending, Header:=xlGuess, _
      OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
      Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

  ' clean up
  Set currWorksheet = Nothing

End Sub

应该没有理由使用 With这里。这是使用 vba - Application.AddCustomList 进行自定义排序的更好方法。只是一种糟糕的做事方式——非常不友好