data validation list dont show blanks

[gheyman]

I have a data validation list in one of my cell. The list range that it is looking at A1:A57 has some blank cells. I don't want the pull-down list (From the Validation list) to show any blanks. Is there a way to do this?

If not is there another way to do this

 

[Joe4]

See here: https://www.myonlinetraininghub.com/excel-ignore-blanks-in-data-validation-list

 

[gheyman]

What if there are rows that are not real blanks? They are formulas that retuned "" blanks? This links formula only works if its truly a blank. Thanks Joe

 

[Joe4]

What if there are rows that are not real blanks? They are formulas that retuned "" blanks? This links formula only works if its truly a blank

Doesn't the "When a Blank Cell Isn't Really Blank" section of that link address that precise situation?

 

[gheyman]

Unfortunately it doesn't. The title "When a Blank Cell Isn’t Really Blank" may lead you to believe that but its not exactly accurate.

This link shows you how to create a Validation list "Without Blanks" it does it in two steps.

First (Step 1) it takes the data from your spreadsheet row and consolidates it so that it doesn't have blanks.

Then (Step 2) it shows you how to name the new consolidate row using a dynamic name ranging formula. This is where you see the title ""When a Blank Cell Isn’t Really Blank" this formula in step two leaves off the blank rows (rows where there are formulas that result in "") from the named range.

So, step two is only valuable if you can get past step one. In step one their formula only consolidates the original list if the blank rows were actually blank and not blank from a formula. So if you cannot consolidate your original list in step one, then step two is useless.


To see what I am saying; create a list in column A. But insert blanks in-between the data (using a formula so these blanks are actually "") Something like in cell A3 add the formula =If(B3 = "","",B3) and leave cell B3 empty. This will put "" in A3

A2 Jim
A3 "" ("Blank" value from a formula)
A4 Bob
A5 Joe
A6 "" ("Blank" value from a formula)
A7 John
A8 Dave

in this situation, which is what I am working with, this attachment doesn't help - if I am reading everything correctly.

 

[Joe4]

OK, I do not know how to do it by formula then. I would probably resort to VBA.
I would probably do it something like this: https://www.mrexcel.com/forum/excel-questions/270958-vba-populate-data-validation-drop-down.html, slightly modify the code to only add the value if it is not blank.

 

[Joe4]

I modified that code for you, and here is what I came up with:

Sub CreateValidationList()

    Dim lr As Long
    Dim r As Long
    Dim FormulaBuilder As String
    Dim vldRng As Range
    
'   Set cell that you want to put this validation list in
    Set vldRng = Range("C2")
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row

'   Loop through all rows, starting in row 2
    For r = 2 To lr
        If Cells(r, "A") <> "" Then
            FormulaBuilder = FormulaBuilder & Cells(r, "A") & ","
        End If
    Next r

'   Strip off ending comma
    FormulaBuilder = Left(FormulaBuilder, Len(FormulaBuilder) - 1)

'   Place cell validation
    With vldRng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=FormulaBuilder
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub

You will obviously need to update the range references to match your sheet.

 

[gheyman]

Thank you. That was very nice of you. I will give this a try now.

 

[gheyman]

Joe;

What if the source for the list is on a different tab?

I need the Validation List to be on my tab named "54_TPL_01_02" but the data that the validation list is made up of is on a tab nambed "Drop_Down_LISTS" in column Q in Cells 3 down (Q3)

Sheets("Drop_Down_LISTS").Range(Q3:Q)

Sub CreateValidationList()

    Dim lr As Long
    Dim r As Long
    Dim FormulaBuilder As String
    Dim vldRng As Range
    
'   Set cell that you want to put this validation list in
    Set vldRng = Sheets("54_TPL_01_02").Range("G11:G500")
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "Q").End(xlUp).Row

'   Loop through all rows, starting in row 3
    For r = 3 To lr
        If Cells(r, "Q") <> "" Then
            FormulaBuilder = FormulaBuilder & Cells(r, "Q") & ","
        End If
    Next r

'   Strip off ending comma
    FormulaBuilder = Left(FormulaBuilder, Len(FormulaBuilder) - 1)

'   Place cell validation
    With vldRng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=FormulaBuilder
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub

Thanks Again

 

[Joe4]

You need to be on the drop-down sheet when building the list. You can do that by adding one line of code.
You can activate the other sheet at the end of the code, if you like, if you want it to finish on the sheet with the drop-downs.

Sub CreateValidationList()

    Dim lr As Long
    Dim r As Long
    Dim FormulaBuilder As String
    Dim vldRng As Range
    
'   Set cell that you want to put this validation list in
    Set vldRng = Sheets("54_TPL_01_02").Range("G11:G500")
    
'   Find last row with data in column Q
[COLOR=#ff0000]    Sheets("Drop_Down_LISTS").Activate[/COLOR]
    lr = Cells(Rows.Count, "Q").End(xlUp).Row

'   Loop through all rows, starting in row 3
    For r = 3 To lr
        If Cells(r, "Q") <> "" Then
            FormulaBuilder = FormulaBuilder & Cells(r, "Q") & ","
        End If
    Next r

'   Strip off ending comma
    FormulaBuilder = Left(FormulaBuilder, Len(FormulaBuilder) - 1)

'   Place cell validation
    With vldRng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=FormulaBuilder
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

End Sub