Results 1 to 3 of 3
Thread: Data Validation/List Question
2014-01-13, 15:34 #1
- Join Date
- May 2002
- Thanked 0 Times in 0 Posts
Data Validation/List Question
I am using a list for data validation from an existing table and the table has empty cells, which makes for a long dropdown box. For example:
Is there a way to remove the blank cells from the dropdown box? I donít want to do a copy/paste/sort to remove the blanks as we need to keep them in a non-alpha order. Copying table data to a named range without the blank cells would work, but I canít figure out how to do it.
2014-01-13, 16:30 #2
- Join Date
- Jan 2001
- La Jolla, CA
- Thanked 63 Times in 59 Posts
If you filter the list with blank cells, you can display only those non-blank cells, copy and paste them (including the header) to another column (I put them in F).
Use that column as your data validation.
Then, in data validation, use a list with this type of reference: =OFFSET($F$2,0,0,SUMPRODUCT(- -($F$2:$F$16<>"")),1)
(I put the pasted list in the F column. This formula should remove the trailing blanks at the bottom of the list of 16.)
(I assumed there was a header in F1)
Last edited by kweaver; 2014-01-13 at 16:34.
2014-01-15, 22:48 #3
- Join Date
- Aug 2010
- Pa, USA
- Thanked 668 Times in 609 Posts
A Vb approach
The code below will cycle through the validation list building a new validation list without blanks by assigning it to a variable string. It will then create the validation list for the selected cell by inserting the variable as a comma delimited list. The code can be used repeatedly to set up your sheet by changing the range for the validation list and/or selecting a new cell and running the code. I have included buttons to create and remove the data validation.
Place in a standard module:
Sub CreateValidation() 'DECLARE AND SET VARIABLES Dim rng As Range Dim cell As Range Set rng = Worksheets("Sheet1").Range("D2:D16") 'CHANGE RANGE Dvalidation = "" '------------------------------- 'FILTER BLANK CELLS FROM VALIDATION DATA SET For Each cell In rng If cell <> "" And Dvalidation = "" Then Dvalidation = cell ElseIf cell <> "" And Dvalidation <> "" Then Dvalidation = Dvalidation & ", " & cell End If Next cell '------------------------------ 'CREATE CELL VALIDATION With ActiveCell.Validation .Delete .Add Type:=xlValidateList, Formula1:=Dvalidation End With End Sub
Last edited by Maudibe; 2014-01-15 at 22:56. Reason: add file
The Following User Says Thank You to Maudibe For This Useful Post: