removing blanks and duplicates in drop down list (with attached workbook)

Zebrasam,

Are you willing to use towards your solution? When I look at the validation that you currently have set, it seems to refer to information already entered into column B. So, as new items are entered, they become part of the list. Is that what you would LIKE to happen? If so, try the following suggestion, which requires some simple VBA.

First, delete all of the current validation that you have in Column B.
Next, right click on the Sheet tab (where it says Sheet1), and choose View Code.
Next, enter the following code:

Please Login or Register  to view this content.

Now, whenever you select a cell in column B, a drop down with all previously entered items will automatically pop open.

Does this work for you?

This does work for me thanks! On my real spreadsheet however (and not this fictional one) I have multiple columns I would like to apply this to. Also the top row is frozen so I want to exclude the top row from the code.

Can you be more specific about which columns this code should work with?

columns d, e, f, g, h, j, k (not a, b, c and i)

I revised the code to only work on the requested columns, and to also ignore row 1

Does this work?

Please Login or Register  to view this content.

It all works except the first row is not being ignored.

However if it is too much trouble it is still functional with the code as is. However, if the solution is easy let me know

The code SHOULD ignore the first row as it sits. Is the first row of your data in Row 1 in Excel (ie, does it start with Cells A1, B1, etc)?

If not, we have to change the third line of code (starts with "If Target.Row = 1....)

Change the 1 to whichever is the first row of your data.

If this still doesnt work, can you post a sample of your workbook and I'll try and determine the cause

Filtering is enabled in all the cells of the 1st row. When I turned off filtering the problem was solved. However I need filtering enabled.

Also, the fonts in the drop down box are too large and the drop down boxes are a bit narrow. Anyway I can change this?

Oops turning off filtering makes no difference...I'm going to check again

Hi I have uploaded a file that I tested with your code. It seems that if the contents of the b column or the c column are deleted then the problem with the first row appearing in the drop down lists disappears. I have uploaded the excel spreadsheet without your code. If you apply your code to it perhaps you will see the problem.

I think I understand the problem.

With this code, we are using Excel's built-in "Pick from List" feature to create data lists for the user. Unfortunately, to the best of my knowledge, we cannot change the font within these drop down boxes.

Also, with this built in feature, Excel picks items that it believe are part of the list. If you don't want the first row included in the lists, Excel has to recognize that Row 1 is a header row. In order to do so, there should be a heading in row 1 of each column that has corresponding data. I tested the file by including headers in cell B1 and C1, then the items in rows 1 were no longer added to the drop down.

If for presentation purposes you do NOT want any data in Cells B1 or C1, you can simply put in a single space and that should do the trick.

Let me know if it helps

Thanks that solved the problem.
If we cannot change the font size is there any possibility of making the drop down lists wider?

I believe the drop down lists are as wide as the cell itself. If you expand the column, the drop down will also expand

Yes you are right about the width. I think it is now working perfectly. Thanks so much for your help with this!

Take Care

Sam

Hi,

I ran into a problem. If you look at the "test" spreadsheet I attached previously you will notice that even if all the headings are filled in the first row, the drop down list only works for the 3rd row. If you don't fill in cells on the 3rd row and want to fill in cells on the 4th, 5th, 6th rows etc...nothing appears in the drop down list.

What I have done is do a search and replace for all the blank cells. I put a star like this * in each blank cell and that has solved the problem temporarily.

It seems like your formula doesn't like blank cells.

Is there anyway we could fix this?