Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove Blank Entries (No VBA) (2000)

    Can anyone think of a way to use only cell formulas (no macros, VBA, etc.) to reorder a list that has a list of names from cell A1:A10. Some of the cells will have a name in it, some will be blank. I want to remove, or at least move to the bottom, the blank cells so that all the names are listed first. Order of the names is unimportant.

    EX:
    Jeff
    Jack
    David
    (blank)
    (blank)
    Matt

    What I want is:
    Jeff
    Jack
    David
    Matt
    (blank)
    (blank)

  2. #2
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Blank Entries (No VBA) (2000)

    I highlighted A1:A10, and did Data - Sort. The blanks are at the bottom.....and David appears before Jack...which doesn't matter.

  3. #3
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Blank Entries (No VBA) (2000)

    Thanks for the reply. I apologize that I wasn't clear enough with what I was trying to accomplish. I would like to be able to create a formula(s) in a cell(s) to sort the list. I don't want to use VBA, macros, or any built in function where an end user would have to do anything. I would like the data automatically updated when it is pulled from it's source.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Blank Entries (No VBA) (2000)

    No built in functions...so the menu system is out.
    It is supposed to be automatic, but without VBA....I think these are conflicting requirements. Without VBA, you lose the automatic part.
    A formula in and of itself would get you halfway there, something along the lines of =IF(A1<>'',1,2) would go in B1. You'd still need some type of sort routine. As I wrote earlier, the formula isn't necessary, but I can't imagine the automatic magic without code.

    We'll see what others have to say...

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Blank Entries (No VBA) (2000)

    You cannot use formulas to sort a range in place. You can, however, use formulas to create another range that contains the sorted data. See the attached workbook.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Blank Entries (No VBA) (2000)

    As a couple of people have already told you, you can't really do what you are asking without VBA. The attached workbook has a VBA Worksheet Change event routine that will automatically sort A2:A65536 if any cell in that range is changed. Try entering a name anywhere in column A, or deleting any name in column A and see what happens. I think this does exactly what you want, but it does use VBA.
    Legare Coleman

  7. #7
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Blank Entries (No VBA) (2000)

    Hans... You're a genius!! This is exactly what I want!

    Legare, thanks for the VBA code. I'm sure it will come in handy in the future too!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •