Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Hide special items from a Dropdown list in excel

0

 Hi!

I have a dropdown list with a lot of items, however I want to hide some items it should not shown in the dropdown list, let's say all items that start with a "R" should be hidden, is it possible to do this?

Answer
Discuss

Discussion

The answer is yes and no. You can't hide items form a dropdown list. But you can set the dropdown list on the fly. For example, you can set the list only at the time when the user clicks on it, depending upon criteria prevalent at that moment. The process would be a little different depending upon the type of dropdown you have (data validation, ActiveX or form control) but in every case you need a list somewhere from which you can quickly create an array which you assign to the control.
Variatus (rep: 4889) Aug 8, '17 at 10:09 pm
Add to Discussion

Answers

0
Selected Answer

All cells what you want it should not be in the dropdown, add the letter "z" to the beginning, and sort the column Ascending, so all with the "z" will be at the bottom, now add this formula in the define name,and then add the name in the data validation,and all cells that starts with a "z" will not appear in the dropdown 

=OFFSET(Sheet1!$A$3,0,0,COUNTA($A$3:A200)-COUNTIF($A$3:A200,"Z*"),1)

Discuss
0

You can make the drop-down list point to a range that has formulas referencing the original range of values and then make it so those formulas return a blank value as desired. The values then won't appear in your list.

Also you can sort the results so the blank values appear at the bottom of the drop-down list.

These methods aren't perfect but they are easy to execute.

Discuss
0

Try this file

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login