EN VI

Excel VBA Custom Sort?

How to Excel VBA Custom Sort

I have the E3:E300 range with numbers. I want to use a custom sort for it:

1 to 50, then 701 to 720, then 51 to the remaining numbers.

With ActiveSheet.Sort
  .SetRange Range("A3:AA300")
  .SortFields.Add Key:=Range("E3"), Order1:=xlAscending  
  .Header = xlYes
  .Apply
End With

How to do that?

Solution:

You can achieve this by formula:

=LET(data,A2:C301,idxSortColumn,3,
sortColumn,CHOOSECOLS(data,idxSortColumn),
sort1,SORT(FILTER(data,sortColumn<=50),idxSortColumn),
sort2, SORT(FILTER(data,(sortColumn>=701)*(sortColumn<=720)),idxSortColumn),
sort3,SORT(FILTER(data,((sortColumn>50)*(sortColumn<701))),idxSortColumn),
VSTACK(sort1,sort2,sort3))

Adjust data and idxSortColumn to your needs.

You could use this formula in VBA to insert the sorted array and

Sub sort()

Dim rgSort As Range
Set rgSort = ActiveSheet.Range("A2:C301")

Dim sortFormula As String
sortFormula = "=LET(data," & rgSort.Address & ",idxSortColumn,3," & vbLf & _
    "sortColumn,CHOOSECOLS(data,idxSortColumn)," & vbLf & _
    "sort1,SORT(FILTER(data,sortColumn<=50),idxSortColumn)," & vbLf & _
    "sort2, SORT(FILTER(data,(sortColumn>=701)*(sortColumn<=720)),idxSortColumn)," & vbLf & _
    "sort3,SORT(FILTER(data,((sortColumn>50)*(sortColumn<701))),idxSortColumn)," & vbLf & _
    "VSTACK(sort1,sort2,sort3))"
    

With rgSort.Offset(, 4).Resize(1, 1)
    .Formula2 = sortFormula

    rgSort.Value = .SpillingToRange.Value
    
    .Clear
End With
    
End Sub