VBA - Data validation empty cell

Question

I created named ranges in excel, naming of range is based on key values on the sheet2. Now I created dropdown list on another sheet1, with formula usage - INDIRECT, based again on key, on sheet1 . How I can add blank/special symbol to dropdown list? I cant add empty cell between the sorted items on sheet. Sheet2:

I have 2 named ranges based on MAT/AE columns, first one is range of C2:C4, and the next one is C5:C6.

And there I have Sheet1, I using data validation , concatenating MAT1&AE11 using formula INDIRECT and i have values based on ranges from Sheet2.

SO my question is, how to add blank/special character to this list?

Code for ranges :

    Sub Start()

lf_index_row = 1
lf_name_space_row = 2

gf_namespace = ""

Do

lf_index_row = lf_index_row + 1

lf_material = Sheets(gc_data).Cells(lf_index_row, 1)
lf_location = Sheets(gc_data).Cells(lf_index_row, 2)

gf_new_namespace = "X" & lf_material & lf_location

If gf_new_namespace = "X" Then
 If gf_namespace = "" Then
    End
 Else
    'create namespace
    Set lf_range = Range(Cells(lf_start_number, 3), Cells(lf_end_number, 3))
    lf_range.Select
    Range(Cells(lf_start_number, 3), Cells(lf_end_number, 3)).Select
    ActiveWorkbook.Names.Add Name:=gf_namespace, RefersTo:=lf_range
    End
 End If
End If
If gf_namespace <> gf_new_namespace Then
    If gf_namespace = "" Then
        'initialize newnamespace
        gf_namespace = gf_new_namespace
        lf_start_number = lf_index_row
        lf_end_number = lf_index_row
    Else
        'create namespace
        Set lf_range = Range(Cells(lf_start_number, 3), Cells(lf_end_number, 3))
        lf_range.Select
        Range(Cells(lf_start_number, 3), Cells(lf_end_number, 3)).Select
        ActiveWorkbook.Names.Add Name:=gf_namespace, RefersTo:=lf_range
        'initialize newnamespace
        gf_namespace = gf_new_namespace
        lf_start_number = lf_index_row
        lf_end_number = lf_index_row
    End If
Else
    lf_end_number = lf_index_row
End If

Loop

End Sub

Indirect formula :

Definition of first named range :

I can remove it, but its not working because in my ranges there is no blanks — t0rick
What is it you want to do then? Not modify the range on Sheet2 but add one or more values directly to the dropdown? Possibly dynamically? Why can't you place your special characters within the named range? — Inarion
How I can add them if Im using formula INDIRECT in dropdown list, may be there is something I dont know? — t0rick
It still not clear to me what your intention is. You want to achieve an extra row in your data validation dropdown, correct? And this extra row should contain what exactly? Nothing? Special characters (which ones)? And you can not add these to your existing named ranges (why not)? You have to see that aside from what you tell us here, we know nothing about your project, its constraints and requirements, and what you want to achieve. — Inarion

Vityata Vityata · Accepted Answer · 2018-04-30T12:44:20

If the list is in Range("A1:A10") this is how to achieve a validation list with only one empty position:

with the following code:

Sub TestMe()

    Dim list1               As Range
    Dim validationFormula   As String

    Set list1 = Range("A1:A10")

    Dim myCell As Range
    For Each myCell In list1
        If Not IsEmpty(myCell) Then
            validationFormula = validationFormula & myCell.Value2 & ","
        End If
    Next

    validationFormula = validationFormula & Chr(160)

    With Range("B5").Validation
        .Delete
        .Add Type:=xlValidateList, Operator:=xlBetween, Formula1:=validationFormula
        .IgnoreBlank = False
        .InCellDropdown = True
    End With

End Sub

What is the idea of the code? The validation string is made in the validationFormula, through concatenating all the cells that are Not IsEmpty(). Once the validationFormula is ready, Chr(160) is added to it, to make sure that we have the empty cell available as well.

It can be added even like this: validationFormula = Chr(160) & "," & validationFormula, if you need to have it at the first position:

Once the validationFormula string is prepared, we can allow ourself to write .IgnoreBlank = True, as far as there is only one blank in the list - the one we need.

Credits to this guy here, for the looping idea - https://superuser.com/questions/1254754/data-validation-from-2-lists-excel-2010

VBA - Data validation empty cell

2 Answers