Sort a column in descending order, putting strings at bottom

[mcomp72]

I sort a column in descending order via VBA code. Sometimes there's a string value in the column that begins with a number. It always put that value at the top when it sorts in descending order. Is there a way to get it to put that at the bottom instead? I tried to do it manually, but could not find how to do it.

Here's an example of what my column might look like.

I then told it to Sort, like this:

When I clicked OK, this is what is did:

I would like "2nd game" to be at the bottom. Is there a way to tell Excel to put all of the non-numeric values at the bottom of the sort, even in descending order?

 

[kevin9999]

If you're OK with a VBA solution, then try the following (assumes your data is exactly as you've shown in your original post).

Sub SortTwice()
    With Range("A1").CurrentRegion.Resize(, 2)
        .Columns(2).FormulaR1C1 = "=if(isnumber(rc1),1,0)"
        .Sort Key1:=.Cells(1, 2), order1:=xlDescending, key2:=.Cells(1, 1), order2:=xlDescending, Header:=xlNo
        .Columns(2).ClearContents
    End With
End Sub

 

[mcomp72]

If you're OK with a VBA solution, then try the following (assumes your data is exactly as you've shown in your original post).

Sub SortTwice()
    With Range("A1").CurrentRegion.Resize(, 2)
        .Columns(2).FormulaR1C1 = "=if(isnumber(rc1),1,0)"
        .Sort Key1:=.Cells(1, 2), order1:=xlDescending, key2:=.Cells(1, 1), order2:=xlDescending, Header:=xlNo
        .Columns(2).ClearContents
    End With
End Sub

Ah... I see what you did there. So basically, I need a "helper" column to notate which columns are numeric and which are not.

The columns on my actual sheet are slightly different, but I see what the code is doing. I'll give that a shot. Thanks!

 

[Peter_SSs]

If you wanted to do it without a helper column you could test this.
I have assumed

• That you would not be doing this if there were no straight numbers so I have not done an error check for that.
• That the numbers are not the result of formulas

Sub Sort_Special()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
    .SpecialCells(xlConstants, xlNumbers).Sort Key1:=.Cells(1), Order1:=xlDescending, Header:=xlNo
  End With
End Sub

You could of course do the same thing manually in 2 steps.
Select all the data and sort Ascending
Select the numbers only, which will now be grouped together, & sort Descending

Before:

mcomp72.xlsm
	A
1	5
2	xyz
3	7
4	1
5	3
6	9
7	2nd game
8	abc
9	11
10	2
11	6
Sheet1

After:

mcomp72.xlsm
	A
1	11
2	9
3	7
4	6
5	5
6	3
7	2
8	1
9	2nd game
10	abc
11	xyz
Sheet1

 

[mcomp72]

If you wanted to do it without a helper column you could test this.
I have assumed

• That you would not be doing this if there were no straight numbers so I have not done an error check for that.
• That the numbers are not the result of formulas

Sub Sort_Special()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
    .SpecialCells(xlConstants, xlNumbers).Sort Key1:=.Cells(1), Order1:=xlDescending, Header:=xlNo
  End With
End Sub

Before:

mcomp72.xlsm
	A
1	5
2	xyz
3	7
4	1
5	3
6	9
7	2nd game
8	abc
9	11
10	2
11	6
Sheet1

After:

mcomp72.xlsm
	A
1	11
2	9
3	7
4	6
5	5
6	3
7	2
8	1
9	2nd game
10	abc
11	xyz
Sheet1

Thanks!

 

[Peter_SSs]

Thanks!

You're welcome. You may have missed my edit a moment ago to say that it would be pretty easy manually too, since that seems what you were trying to do originally.