VBA, Sorting based on custom list

Question

I am trying to sort my records based on Col A values, there are 5 different values and many rows (in a table). Also I have the custom list created in excels built in sort feature.

I am getting an error Sort method of range class failed on

oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Here is my code:

Sub Sort()



Dim oWorksheet As Worksheet
Set oWorksheet = ActiveWorkbook.Worksheets("database")
Dim oRangeSort As Range
Dim oRangeKey As Range

' one range that includes all colums do sort
Set oRangeSort = oWorksheet.Range("A2:FR20000")
' start of column with keys to sort
Set oRangeKey = oWorksheet.Range("A2")

' custom sort order
Dim sCustomList(1 To 5) As String
sCustomList(1) = "sort1"
sCustomList(2) = "sort2"
sCustomList(3) = "sort3"
sCustomList(4) = "sort4"
sCustomList(5) = "sort5"

Application.AddCustomList ListArray:=sCustomList


oWorksheet.Sort.SortFields.Clear
oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

' clean up
ActiveSheet.Sort.SortFields.Clear
Application.DeleteCustomList Application.CustomListCount
Set oWorksheet = Nothing

End Sub

You should know if you have a header or not. Never let Excel xlGuess in a programmed sort. — user4039065
– user4039065, Commented Jul 16, 2018 at 16:46
@Jeeped, do you know the syntax for knowing there is a header? I always have headers. — excelguy
– excelguy, Commented Jul 16, 2018 at 16:47
That was not actually intended as an answer, only a comment to improve your code toward a 'best practices' ideal. See below for legitimate response. — user4039065
– user4039065, Commented Jul 16, 2018 at 16:58

user4039065 · Accepted Answer · 2018-07-16 16:56:45Z

2

Try a VBA sort as opposed to rewriting the recorded sort code.

Sub custom_sort()
    Dim vCustom_Sort As Variant, rr As Long

    vCustom_Sort = Array("sort1", "sort2", "sort3", "sort4", "sort5")
    Application.AddCustomList ListArray:=vCustom_Sort

    With ActiveWorkbook.Worksheets("database")
        .Sort.SortFields.Clear
        rr = .Cells(.Rows.Count, "A").End(xlUp).Row
        With .Range("A2:FR" & rr)
            .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, DataOption1:=xlSortNormal, _
                        Orientation:=xlTopToBottom, Header:=xlYes, MatchCase:=False, _
                        OrderCustom:=Application.CustomListCount + 1

        End With
        .Sort.SortFields.Clear
    End With

End Sub

answered Jul 16, 2018 at 16:56

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

excelguy Over a year ago

Thanks jeeped, I am having an issue, the first row (a2) of data (a1 is headers) is staying put and everything else is sorting. Do you know why this is?

Collectives™ on Stack Overflow

VBA, Sorting based on custom list

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related