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.

Mr Patel

Mrs Patel

Mr Lang

Mrs Li

Mrs Smith

Mr Lewis

Mrs Lewis

Mrs Jones

Mr Smith

<colgroup><col></colgroup><tbody>
</tbody>

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