Data Validation to Create Drop Down List

[KO69]

Dear Potential Helper,

I am in the process of creating a database, and have used 'Data Validation' to create drop down lists in order to obtain information quickly from the database.

I have encountered an issue where if I have a list the resembles the one shown below, the drop down list will show spaces.
[TABLE="width: 97"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Mr Patel[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Mrs Patel[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Mr Lang[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Mrs Li[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Mrs Smith[/TD]
[/TR]
[TR]
[TD]Mr Lewis[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Mrs Lewis[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Mrs Jones[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Mr Smith[/TD]
[/TR]
</tbody>[/TABLE]

This is particularly an issue for myself and my database because there is over 1,000 items in the list, and there are countless empty cells within the list. I have tried sorting the list, BUT because the database is quite large, sorting it will disrupt other functions of the database.

Ideally, I would like the drop down list to show only entries within the list that are not empty so the drop down list is entirely relevant, and more user friendly.

I hope there is a way to resolve this issue (and would suspect it involved VBA), and would appreciate greatly any help.

Many Thanks,

KO69

 

[Shweta]

Hi,

Hope this will help you Excel VBA Tips: PASTE NON BLANK CELLS ONLY USING EXCEL

OR

Excel VBA Tips: CODE TO PASTE NON BLANK CELLS ONLY

 

[KO69]

Hi Shweta,

I am unable to 'copy and paste non-blank cells' given that I have around 50-60 list ranges that are defined that are obtained from one spreadsheet that is updated regularly.

If I was to copy and paste non-blank cells, it would result in the formatting of my database being lost, and would also take considerable amount of time to do it for 50-60 list ranges.

Is it possible to suppress blank cells from the drop down list obtained from using data validation via VBA for example.

Many Thanks,

KO69