+ Reply to Thread
Results 1 to 11 of 11

How to remove blanks in the drop down list?

  1. #1
    Registered User
    Join Date
    04-09-2009
    Location
    Beijing, China
    MS-Off Ver
    Excel 2007
    Posts
    3

    Unhappy How to remove blanks in the drop down list?

    Hi
    When using the data validation for a drop down list, I can't remove the blank entries. Is there any solution to this? I searched a few methods but none worked. There are two scenarios:
    1. three cells as the source:
    non-blank blank blank
    2. and one combination cell and one non-blank cell.

    please see my attachment for details. I'm using Excel 2007.
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to remove blanks in the drop down list?

    dont put blanks in the list! stick it somewhere out of range with no spaces in it.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How to remove blanks in the drop down list?

    See attached...

    Check the formulas in J1:L2 which relists the data with no blanks.. Note that they are array formulas and were confirmed with CTRL+SHIFT+ENTER not just ENTER... if you change anything you need to reconfirm that way

    Then I created to Defined Ranges in the Insert|Name|Define called List1 and List2 and these were referenced in the Data Validation Lists, respectively...
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to remove blanks in the drop down list?

    blimey what a convoluted approach! but useful!

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How to remove blanks in the drop down list?

    Quote Originally Posted by martindwilson View Post
    blimey what a convoluted approach!
    Well, I am kind of assuming, as is usually the case, that the actual problem is more complex than the OP has shown and that really there could be a larger range with multiple blanks.. and that the OP cannot "manually" list the items without blanks as you suggest...

    but, hey, if the OP likes your solution... then that is why my sig says as it does....

  6. #6
    Registered User
    Join Date
    04-09-2009
    Location
    Beijing, China
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to remove blanks in the drop down list?

    Quote Originally Posted by martindwilson View Post
    dont put blanks in the list! stick it somewhere out of range with no spaces in it.
    Yeah...I wish...But I had to deal with them. I can't decide the form of sources for the drop down list. They were designated.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How to remove blanks in the drop down list?

    Have you seen/tried my solution?

  8. #8
    Registered User
    Join Date
    04-09-2009
    Location
    Beijing, China
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to remove blanks in the drop down list?

    Quote Originally Posted by NBVC View Post
    Well, I am kind of assuming, as is usually the case, that the actual problem is more complex than the OP has shown and that really there could be a larger range with multiple blanks.. and that the OP cannot "manually" list the items without blanks as you suggest...
    Hi NBVC,
    Thank you. It is not quite complex. The real situation is one row or column may contain repetition of one kind of those two units. Sorry I didn't make it very clear. Extracting them to separate fields as a new source is a way of doing this. Is there any possibility that I can just keep the sheet as it is, I mean, without extracting to add new content. Write a new function or some other ways using VBA?

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How to remove blanks in the drop down list?

    It is more complex then... you will need VBA..

    Here is one I found googling.. maybe you can adapt it or hope someone gives a solution here:

    http://www.ozgrid.com/forum/showthread.php?t=59450

  10. #10
    Registered User
    Join Date
    10-14-2010
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel Mac: 2008
    Posts
    4

    Re: How to remove blanks in the drop down list?

    What's the equivalent formula for removing blanks from a data validation list that is referencing data in a single column as opposed to a single row?

  11. #11
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: How to remove blanks in the drop down list?

    Moxcel

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1