sort blank cells first instead of last

  • Thread starter Thread starter Paul Simon
  • Start date Start date
P

Paul Simon

There have been a few solutions (mostly manual) posted for sorting a
database with blank cells in the chosen sort fields being sorted first
instead of last. I'm trying to come up with a more automated method
but am stumped on the first part of my method which involves a
UserForm.

Using Excel 2000, I'm trying to create a UserForm which as closely as
possible looks and acts like Excel's standard Sort dialog box and then
use that UserForm in place of the Sort dialog box whenever I want
blank cells sorted first instead of last.

Like the Sort dialog box, this UserForm would let you choose up to 3
fields and designate either Ascending or Descending for each and
perhaps even allow for indicating "Header Row" or "No Header Row".
(Duplicating all the options that come up when clicking the Options
button in the Sort dialog box would be "pushing the envelope" too
much, I'm sure.)

Code associated with this UserForm would do the following:
1. Trap whatever fields were chosen in that UserForm
2. Fill any blank cells within those fields with a very large (or
should I say very small) negative number (such as -9999999999) to
insure they get sorted first
3. Sort the database
4. Change the fields containing that -9999999999 number back to blank
again
5. Then lastly, unload the UserForm.

I'm kind of weak on this level of UserForm creation. Any help would
be greatly appreciated.

Many thanks,
Paul
 
Paul,

Not the complete answer, but this will make it easier.
Empty cells contain a NUL ("") and excel sorts them last.
Cells containing a space(" ") are sorted first (before any other printable
characters)
So, instead of filling your empty cells with a small number, just put in one
space.
The cells still look empty!
No need to remove the space at the end unless you need the cell blank (Nul)

HTH
Henry
 
Hi Henry,

Thanks for the suggestion. I appreciate your taking the time to
respond.

While the space character does get sorted before other text, it still
gets sorted after numbers. Thus the need for a very large (or very
small, depending on your viewpoint) negative number like -9999999999
to insure sorting at the top.

But thanks anyway.

Paul
 
Sorry Paul,

I was under the false impression that Excel sorted things in ASCII order
and, as space is the first printable character in Ascii, that would come
before the numbers.

Try putting an apostrophe into the begining of each cell in your range (i.e.
'1234, 'Text, etc.)
This has the effect of converting blank cells and numbers to text.
Do your sort and then remove the apostrophes.

I just tried it with a few cells (a mixture of text, numbers and blanks) and
it worked OK for me. (XL XP, XP Pro.)

Now all you've got to do is write the VBA to do it!!

HTH
Henry
 
Back
Top