Excel Macro add data validation list box to sheet - Blank cells

Question

I am adding a data validation list box to an existing Excel spreadsheet. The data is coming from a separate sheet called Data. The range for the data is A4 - A100; so far data is only in cells A4 - A10.

When activating the Data Validation List box it is reading all the blank cells. Is there a way to stop this?

My code so far:

    ''Code below is what I am using to create the list box
    With Range("A21:A42").Validation

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Data!$A$4:$A$100"
    End With

I know that when creating Data Validation directly in Excel it's a tick box to ignore blank cells.

Answer 1

You can add the ignore blank part in the code: eg.

With Range("A21:A42").Validation
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Data!$A$4:$A$100", .IgnoreBlank = True
End With

You can also get these code, by recording a macro

Answer 2

Hi You can create a Dynamic Named Range

Goto Name Manager anc create new name example filteredList

then use formula for the list

=Data!$A$4:INDEX(Data!$A$4:$A$100,SUMPRODUCT(--(Data!$A$4:$A$100<>"")))

and later update your macro or the range from =Data!$A$4:$A$100 to =filteredList

If you wanted to add the List using vba use below code above your code

ActiveWorkbook.Names.Add Name:="filteredList", RefersToR1C1:= _
    "=Data!R4C1:INDEX(Data!R4C1:R100C1,SUMPRODUCT(--(Data!R4C1:R100C1<>"""")))"
ActiveWorkbook.Names("filteredList").Comment = ""

===

With Range("A21:A42").Validation
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=filteredList"
End With