Solved Excel 2007 How do i sort Blank cells out auto

July 19, 2011 at 13:18:25
Specs: Windows XP
In Excel 2007. How do i sort Blank cells out automaticlly? I saw a code that i thought would work but it sorted incorrectly. i need a sort that skips blanks but maintains the order in which the totals arranged.

1
3

5
7
9

2
4


6
8

1
3
5
7
9
2
4
6
8

i seem to have a problem trying to figure this out.
the code mentioned before is =IF(ISERROR(SMALL($A$1:$A$9,ROW())),"",SMALL($A$1:$A$9,ROW()))

it seems to work partially but it sorts the totals from 1-9 1 is on top and if there are too many blanks it will only show 5-9.


See More: Excel 2007 How do i sort Blank cells out auto

Report •

#1
July 19, 2011 at 17:56:17
✔ Best Answer
If you don't mind a keyboard short cut, look here:

http://chandoo.org/wp/2010/01/26/de...

F5 ALT+s k Enter CTRL+ – u Enter

If you want a formula...

This will work for data in B1:B12 when placed in C1 and dragged (drug?) down. Modify it to fit your needs.

I can not take credit for writing it...merely for finding it via Google:

This is an array formula that must be entered with Ctrl-Shift-Enter

=IF(ROWS(C$1:C1)<=COUNT(B$1:B$12)+COUNTIF(B$1:B$12,"*?"),INDEX(B$1:B$12,SMALL(IF(B$1:B$12<>"",ROW(B$1:B$12)-ROW(B$1)+1),ROWS(C$1:C1))),"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 20, 2011 at 08:10:33
Thank You so much!!! i was stuck and didnt know what i could do. this worked PERFECTLY for what i needed.

again THANK YOU,
XD


Report •
Related Solutions


Ask Question