Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort blank cells first instead of last
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort blank cells first instead of last
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 "Paul Simon" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort blank cells first instead of last
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 "Henry" wrote in message ... 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 "Paul Simon" wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort blank cells first instead of last
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 "Paul Simon" wrote in message m... 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 "Henry" wrote in message ... 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 "Paul Simon" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort a table that contains both numbers and blank cells | Excel Discussion (Misc queries) | |||
sort and blank cells problem | Excel Worksheet Functions | |||
Sort when I have blank cells | Excel Discussion (Misc queries) | |||
sort and sum data range with some blank cells | Excel Worksheet Functions | |||
Why arent my blank cells moved to the end when I do a sort? | New Users to Excel |