CODEWITHSUNDEEP
vbaexcel
t0rick
t0rick

Reputation: 55

VBA - Data validation empty cell

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: enter image description here

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

enter image description here

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 :

enter image description here

Definition of first named range :

enter image description here

Upvotes: 2

Views: 6854

Answers (2)

Vityata
Vityata

Reputation: 43575

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

enter image description here

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:

enter image description here

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

Upvotes: 3

Aaron Watkins
Aaron Watkins

Reputation: 33

Check if cell with row-value = 3 and column-value = 4 is blank with the following:

Set objExcel = CreateObject("Excel.Application")
Set excelInput = objExcel.Workbooks.Open("myfile")
If excelInput.Sheets("Sheet1").Cells(3, 4) <> vbNullString Then
    'do the thing
End If

The above code is VBScript but it should work. If not, its almost identical in VBA.

Upvotes: 0

Related Questions