|re: I can't seem to find an answer online|
You didn't look hard enough! <g>
I stumbled upon something that might work and modified it to fit your needs - as I understand them. Let me know if I made the wrong assumptions, other than specific ranges, of course.
The theory here is that if you create a copy of your list without the blanks and then use that as the source for dropdown, it won't show the blanks.
Here are the assumptions upon which this solution is based:
- Your list of IF statements is in A1:A10
- The "X"'s in IF(X=True,Data,"") are B1:B10. In other words, enter something in B1:B10 and the corresponding cell in A1:A10 will change.
- Your Data Validation list is in C1
Right click the sheet tab for the sheet in question, choose View Code and paste in the code below.
Whenever a change is made to B1:B10, the code will rebuild the Data Validation list in C1 based on the values in A1:A10, leaving out the blanks.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(Target.Address), Range("B1:B10")) Is Nothing Then
Dim r As Range, txt As String
For Each r In Range("A1:A10")
If Not IsEmpty(r) Then txt = txt & "," & r.Value
.Value = Empty
.Add Type:=xlValidateList, Formula1:=Mid(txt, 2)