macro sort not working

Question

I have a few lines in a macro to sort some data. However it is not working as i expect it to.

Key1:=wsh2.Range("C1"), Order1:=xlAscending, _

Key2:=wsh2.Range("D1"), Order1:=xlAscending, _

Key3:=wsh2.Range("L1"), Order1:=xlDescending, _

Key 1 is sorting data from A-Z

Key 2 is sorting data from A-Z as well

Key 3 is supposed to be sorting numbers from highest to smallest but is not working. I have tried both ascending and descending. the numbers range from 0 upwards, and include things such as 0.25

Answer 1

Assuming that you do not have Option Compare Text at the top of the module:

UCase converts strings to UPPER CASE, so the values you compare with should be in upper case too:

    Dim m As Long

    m = wsh2.Range("M" & wsh2.Rows.Count).End(xlUp).Row

    For r = m To 1 Step -1

        If UCase(wsh2.Range("M" & r)) = "N/A" Or _

           UCase(wsh2.Range("M" & r)) = "EXTERNAL" Or _

           UCase(wsh2.Range("M" & r)) = "NOT YET BAU" Or _

           UCase(wsh2.Range("M" & r)) = "OTHER" Then

            wsh2.Range("M" & r).EntireRow.Delete

        End If

    Next r

Or, slightly more elegant

    Dim m As Long

    m = wsh2.Range("M" & wsh2.Rows.Count).End(xlUp).Row

    For r = m To 1 Step -1

        Select Case UCase(wsh2.Range("M" & r))

            Case "N/A", "EXTERNAL", "NOT YET BAU", "OTHER"

                wsh2.Range("M" & r).EntireRow.Delete

        End Select

    Next r

Answer 2

This is a cut down version of my overall table and some data. I have changed the information to make it easier to understand:-

Colour	Drink	mix
red	coke	1.00
red	coke	1.00
red	coke	1.00
red	coke	0.25

For some reason it sorts the first to columns but keeps showing 0.25 as the first record in the last column when i run the macro. It should show 1.00. The macro then deletes all the duplicates so i need the highest number to be first so it is not deleted.

Answer 3

Keep in mind that L1 is the 3rd key, so the rows will be sorted on columns C and D first, and the sort on column L will only be noticeable if there is a series of rows with the same values for both C and D. For example:

C        D        L

-------  -------  -------

Aaa    Aaa    12

Aaa    Bbb    14

Bbb    Aaa    15

Bbb    Ccc    17Bbb    Ccc    16Bbb    Ddd    18

The sort order for column L only becomes apparent for the two rows marked as italic*.***

Answer 4

Yes it works if i do it under the data tab then sorting. The first two sorts are basically putting all the duplicate records together.

Answer 5

Is it sorting correctly if you do it using the Data Tab -> Sort?

It should work if it is working for key1 and 2.

Hope you are aware that Key 3 sorting would be within key1 x key2.