If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sort a table that contains both numbers and blank cells
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? |
#2
|
|||
|
|||
sort a table that contains both numbers and blank cells
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? |
Thread Tools | |
Display Modes | |
|
|