VBA sorting not working properly

Question

This challenge came about following an answer to this question: Excel VBA Sorting

I used the code provided as one of the answers there and attempted to edit it to my situation

I need to sort on column A, header in A1, A to Z - whilst maintaining row integrity, i.e. the row values follow the sorted cells

Here is my attempted code edit, but I've clearly not edited it properly because the column A values are not sorted alphabetically as I'd hoped - can someone please assist?

With ws_catalogue

    '''''''''''''''''''''''''''''''''''''''''''
    'Get range from B1 to last cell on sheet. '
    '''''''''''''''''''''''''''''''''''''''''''
    Set myRange = .Range(.Cells(1, 1), .Cells(.Cells.Find("*", , , , 1, 2).Row, .Cells.Find("*", , , , 2, 2).Column))

    myRange.Select

   With .Sort
        .SortFields.Clear

        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'As using late binding Access won't understand Excel values so: '
        'xlSortOnValues = 0     xlYes           = 1                     '
        'xlAscending    = 1     xlTopToBottom   = 1                     '
        'xlSortNormal   = 0     xlPinYin        = 1                     '
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        .SortFields.Add _
            Key:=myRange.Offset(, 6).Resize(, 1), _
            SortOn:=0, _
            Order:=1, _
            DataOption:=0

        .SetRange myRange
        .Header = 1
        .MatchCase = False
        .Orientation = 1
        .SortMethod = 1
        .Apply

    End With

End With

You have to provide a key (key :=) for the sort in this case Column A values. — Wookies-Will-Code
– Wookies-Will-Code, Commented Feb 7, 2018 at 16:34

Ricardo González · Accepted Answer · 2018-02-07 16:44:04Z

0

If you want column A to be sorted, you need to include it on myRange. Currently you have

ws_catalogue.Sort.SetRange = myRange

Where myRange is from B1 to the bottom of the data (it does not include column A).

Additionally, if you want to order column A, you need to add something like this

    .SortFields.Add _
        Key:=myRange.Offset(0,0) _
        SortOn:=0, _
        Order:=1, _
        DataOption:=0

answered Feb 7, 2018 at 16:44

Ricardo González

1,44311 silver badges21 bronze badges

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

Comments

Wookies-Will-Code · Accepted Answer · 2018-02-07 16:49:52Z

@Xabier has you taken care of but a few other considerations, sorry this code is from another similar answer but it defines the worksheet, and the column names for easier reading code. I will skip the Dims and go to the meat of it, this was for a 3 column sort of unknown or changing row quantity. The example simply shows setting up for the range definition using the rows and columns and Cells to define the range. I stopped the code at the sort, the rest of the code generates a single email for each person with the dept names listed in the body of the email, not relevant to this question. Maybe another way to think about it. You can see the use of the sort key.

'set worksheet to work on as active (selected sheet)
Set emailWS = ThisWorkbook.ActiveSheet
startRow = 2 ' starting row
nameCol = 1 'col of names, can also do nameCol = emailWS.Range("A1").Column
deptCol = 3 'col of depts, can also do deptCol = emailWS.Range("A3").Column
'** Advantage of the optional way is if you have many columns and you don't want to count them

'find the last row with a name in it from the name column
lastRow = emailWS.Cells(emailWS.Rows.Count, nameCol).End(xlUp).Row

'sort the data first before going through the email process using Range sort and a key
'assumes these are the only columns 1 (nameCol) thru 3 (deptCol) to sort
'assumes you are sorting based on col 1 (nameCol)
emailWS.Range(Cells(startRow, nameCol), Cells(lastRow, deptCol)).Sort _
key1:=emailWS.Range(Cells(startRow, nameCol), Cells(lastRow, nameCol))

Cheers, WWC

Xabier · Accepted Answer · 2018-02-09 12:13:28Z

0

If you want to sort by values on Column A, in ascending order, then the following will do that taking the full sheet into consideration:

Sub sortColumnA()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'declare and set your worksheet, amend as required
    If ws.AutoFilterMode = False Then ws.Cells.AutoFilter
    ws.AutoFilter.Sort.SortFields.Clear
    ws.AutoFilter.Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Cells.AutoFilter
End Sub

UPDATE:

If you only want to sort a specific range then something like below should work too:

Sub sortColumnA()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'declare and set your worksheet, amend as required
    If ws.AutoFilterMode = False Then ws.Range("A1:Z1").AutoFilter
    ws.AutoFilter.Sort.SortFields.Clear
    ws.AutoFilter.Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Range("A1:Z1").AutoFilter
End Sub

edited Feb 9, 2018 at 12:13

answered Feb 7, 2018 at 16:35

Xabier

7,7251 gold badge11 silver badges21 bronze badges

9 Comments

Wookies-Will-Code Over a year ago

there you go he provided the key, don't forget to find the last row of data based on the column of your choice so you can dynamically define the range.

Xabier Over a year ago

@Wookies-Will-Code you don't need to define a range to sort, unless there is other data in the same sheet that you would like to exclude from the Sort...

Wookies-Will-Code Over a year ago

I see now how he defined the range. Thank you @Xabier. Good tip.

Ravarro Over a year ago

Thanks Xabier, however when I tried that (with the worksheet amended) I get the error "1004 AutoFilter method of Range class failed"? It points the error at ws.Cells.AutoFilter

Xabier Over a year ago

Updated my answer to check whether the Autofilter is on or off before running the rest of the code..

|

Collectives™ on Stack Overflow

VBA sorting not working properly

3 Answers 3

Comments

Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related