I dont know how many values will be present from Cell A1 to A65555. I have B1 which is set with data validation based on A column as below A1:A65555
Values are present in A column upto 10 rows. (from A1 to A10)
But when i click B1, it shows empty values along with A1 to A10. I want all not null values of A column to be listed in B1.
Note: i have ticked the option "Ignore Blank cells" in Data validation setting. or Any one let me know how to set data validation through VBA ?
assuming you will not have gaps in column A, just some amount of blank rows at bottom of column (as in data will always be A1:AN where 1 < N <= 65555)
Formulas => Define Name
enter some name (like ValidationList)
Use this as your formula =OFFSET(INDIRECT("Sheet1!$A$1"),0,0,COUNTA(Sheet1!$A:$A),1)
Then define data validation list as the the name you entered (and check ignore blanks again so it doesn't give errors when you haven't selected anything)