A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

sort a table that contains both numbers and blank cells



 
 
Thread Tools Display Modes
  #1  
Old October 8th, 2009, 05:49 PM posted to microsoft.public.excel.misc
slickedge52
external usenet poster
 
Posts: 1
Default 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  
Old October 8th, 2009, 06:13 PM posted to microsoft.public.excel.misc
Gary''s Student
external usenet poster
 
Posts: 7,584
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.