Removing Blank cells in pulldown menu

July 9, 2009 at 07:43:00
Specs: Windows XP
OK, I have a question and I can't seem to find an answer online. I have a dropdown menu i want blanks removed from. I have a list which is comprised of IF(X=True,Data,"") and the pulldown menu is linked to that list. When the Validation is untrue, the field is blank, however, the blank field appears in the Pulldown Menu. I want to remove the blanks from the menu, is there a way to do this?

Sorting the list would ruin the data validation.

I am using Excel 2000


See More: Removing Blank cells in pulldown menu

Report •


#1
July 9, 2009 at 10:18:17
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
    Next
    With Range("C1")
        .Value = Empty
        With .Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Mid(txt, 2)
        End With
        .Select
    End With
    End If
End Sub


Report •

#2
July 9, 2009 at 11:57:31
Hmm, I tried it, but I am getting nothing in the C column.

In column A I have:

=IF(B1="YES",Data,"")

in Column B I have:
=IF($D$1=TRUE,"YES","") or
=IF($D$1=TRUE,"NO","")

In Column C I have:
Nothing

I want a list of the YES arguments in Column B without the blank spaces created by the NO ones.


Report •

#3
July 9, 2009 at 12:27:23
You've confused me.

re: I want a list of the YES arguments in Column B without the blank spaces created by the NO ones.

Is the dropdown list created by the results of the formulae in Column A or the formulae in Column B?

Where are you making changes to the Sheet? In other words, what is causing the IF statements to recalculate? Are you entering data in D1?

I assume that you are using proxies for your formulae and I think that that is adding to the confusion.


Report •

Related Solutions

#4
July 9, 2009 at 21:30:57
The dropdown list is created from the results in Column A.

Column B is created from the data in column D1.

The actual formulas are as written, the only proxy I used is the word Data which is a text string.


Report •

#5
July 10, 2009 at 20:41:29
re: Column B is created from the data in column D1.

D1 is a cell, not a column.

I'm a little confused by what you are doing...not that it really matters as far as my helping to create the drop down...

You said: "In column A I have:

=IF(B1="YES",Data,"")

in Column B I have:
=IF($D$1=TRUE,"YES","") or
=IF($D$1=TRUE,"NO","")

and then:

I want a list of the YES arguments in Column B without the blank spaces created by the NO ones.

Why can't you just "create" the blanks in Column A by using:

=IF($D$1=TRUE,DATA,"")

Why do you need to put YES's or NO's in B and then check for just the YES's in A?

Anyway, if D1 is the cell you are changing, then modify the code to look for a change in D1. The dropdown will be created in C1.

Once again, I am assuming a manual change to D1.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$D$1" Then
   Dim r As Range, txt As String
    For Each r In Range("A1:A10")
        If Not IsEmpty(r) Then txt = txt & "," & r.Value
    Next
    With Range("C1")
        .Value = Empty
        With .Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Mid(txt, 2)
        End With
        .Select
    End With
    End If
End Sub


Report •


Ask Question