I have a table that contains both numbers and blank cells, when I sort this
table I first have to do it in ascending order so that the nonblank cells end
up on top, and then I sort again in descending order by highlighting only the
nonblank cells (which are now on top). I want create a macro for this table
so that it will sort automatically but I can't do it because it always puts
the blank cells on top. Is there a way to sort, in descending order, so that
numbers are on top and blank cells are on the bottom?

Say we have a table from C7 thru F20 like:

first sec thir fou
30 622 679 169
3593 934 876 291
2814 501 247 770
939 532 71
4247 818 137 456
9674 917 153 1
4052 754 502 378
40 26 338
6744 14 979 387
9568 502 441 391
27 429 733
7694 203 507 861
4075 55 701 306

running the following macro:

Sub Macro1()

Range("C7:F20").Select

Selection.Sort Key1:=Range("C8"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

For i = 20 To 7 Step -1
If Range("C" & i).Value < "" Then Exit For
Next

If i = 7 Then i = 17

Range("C7:F" & i).Select

Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

will produce:

first sec thir fou
30 622 679 169
2814 501 247 770
3593 934 876 291
4052 754 502 378
4075 55 701 306
4247 818 137 456
6744 14 979 387
7694 203 507 861
9568 502 441 391
9674 917 153 1
939 532 71
40 26 338
27 429 733

--
Gary''s Student - gsnu200907


"slickedge52" wrote:

I have a table that contains both numbers and blank cells, when I sort this
table I first have to do it in ascending order so that the nonblank cells end
up on top, and then I sort again in descending order by highlighting only the
nonblank cells (which are now on top). I want create a macro for this table
so that it will sort automatically but I can't do it because it always puts
the blank cells on top. Is there a way to sort, in descending order, so that
numbers are on top and blank cells are on the bottom?