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, Feb 07 '18 at 16:34

Xabier · Answer 1 · 2018-02-09T12:13:28.577

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 09 '18 at 12:13

answered Feb 07 '18 at 16:35

Xabier

7,587
1
8
20

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. – Wookies-Will-Code Feb 07 '18 at 16:38
@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... – Xabier Feb 07 '18 at 16:41
I see now how he defined the range. Thank you @Xabier. Good tip. – Wookies-Will-Code Feb 07 '18 at 16:45
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 – Ravarro Feb 07 '18 at 16:57
Updated my answer to check whether the Autofilter is on or off before running the rest of the code.. – Xabier Feb 07 '18 at 17:01
Hmm, seem to get the error again but this time on the new line of code: If ws.AutoFilterMode = False Then ws.Cells.AutoFilter – Ravarro Feb 09 '18 at 12:05
@Rath I get no such error when testing, are you trying to sort the full sheet or a specific range only? – Xabier Feb 09 '18 at 12:10
@Xabier the full sheet, however my worksheet only has data in columns A - N. Strangely enough, I modified the code so instead of where it says "A1:Z1" I changed it to "A1:N1" for both instances in the code, and it got as far the '.Apply' row before giving the error message "Method 'Apply' of objet 'Sort' failed"...so it got further ahead, but not sure what the issue is now – Ravarro Feb 12 '18 at 16:35
Updated code: Sub sortColumnA() Dim ws As Worksheet: Set ws = Sheets("Catalogue") 'declare and set your worksheet, amend as required If ws.AutoFilterMode = False Then ws.Range("A1:N1").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:N1").AutoFilter End Sub – Ravarro Feb 12 '18 at 16:36

Wookies-Will-Code · Answer 2 · 2018-02-07T16:49:52.737

@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

score 0 · Answer 3 · answered Feb 07 '18 at 16:44

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

VBA sorting not working properly

3 Answers3