Excel Macro not sorting correctly

Question

I have this script and it sorts MT-001 - MT-999 recently I added two more entries "MT-1000" and "MT-1001" and this is how my Macro is sorting.....

MT-099
MT-100
MT-1000
MT-1001
MT-102
MT-103

My macro for sorting is the following:

Sub FILTERMT()
    Selection.AutoFilter Field:=2, Criteria1:="=*MT*", Operator:=xlAnd
    Range("A7:BZ65536").Sort Key1:=Range("B6"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

Can someone post a code that corrects this error? THX for contributing to this community!

Answer 1

If you notice, MT-099 has a leading zero to ensure it sorts before MT-100 while MT-102 does not have a leading zero to ensure it sorts before MT-1000.

You will need to modify your data to support 4-digit, left zero-padded numbers, or come up with a way to split the numeric portion off into a different column for sorting purposes.

Answer 2

Choose a 3rd column, that may even be a many columns to the right if necessary.

Fill the cells of this column with this formula:

=RIGHT(B6;LEN(B6)-FIND("-");B6))*1

Here, I assume that the top of your list ("MT-099") ist at field B6.

The right expression cuts the number out of the string. The *1 is to really convert to a number. You need only the number treated as a number in order to sort the right way.

Se the formula e.g. in C6 and copy the content downwards to the end row of your list.

Then simply alter your code; here I assume that your new column is C:

Range("A7:BZ65536").Sort Key1:=Range("C6"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

To clarify, here is how it looks like after sorting:

If necessary, you may narrow your 3rd column to zero length, so it is invisible.