Data Validation (Indirect) Remove Blank Cells

[djossh]

I have a Data Validation on "R1" (Using a Name range)

i also have a Data Validation on "M3" (using "=Indirect($R$1)")

My Problem is there are Blank Cells in some of the range..
ie. Column A:D (A=Name1, B=Name2, C=Name3, D=Name4)
Column A Range is A1:A20 (But my list data is up to A1:A12 Only so from row A13 to A20 is Blank)
Column B Range is B1:B20(But my list data is up to B1:B17 Only so from row B18 to B20 is Blank)
Column C Range is C1:C20 (But my list data is up to C1:C8 Only so from row C9 to C20 is Blank)
Column D Range is D1:D20 (list is coplete form D1:D20 so, no Blank fields, this one is Ok)


Are there any VBA code or Macro that will force my Data Validation in "M3" NOT to show Blank cells..Thanks

 

[GlennUK]

Look at this:

http://www.contextures.com/xlDataVal02.html#Dynamic

 

[djossh]

Look at this:

http://www.contextures.com/xlDataVal02.html#Dynamic

I think you are referring to How to create a Dynamic Data Validation.. it does not eliminate Blanks since all samples in your link dont have blank cell in the name range..

You may even go to your link and find a word "Blank" and see if you could find one.

 

[GlennUK]

Your lists are variable length lists of data, that link covers it.