Validation list ignoring empty cells using VBA in Microsoft Excel

In this article, we will use selection change event of the worksheet to create validation list, ignoring blank values.

Raw data consists of Product name, along with some blank cells in column A.

We want to create validation list for the Product name, ignoring blank cells.

Logic explanation

In this article, we have used selection change event of worksheet to create validation in cell I12. Firstly, we will create a string which will comprise of all the product names, separated by commas, ignoring blank cells. Then we will add this string for cell validation.

Code explanation

IntLastRow = .Cells(Rows.Count, 1).End(xlUp).Row

The above code is used to get row number of the last cell.

If Not IsEmpty(.Cells(IntRow, 1)) Then

'Concatening non blank values in the first column

Txt = Txt & .Cells(IntRow, 1) & ","

End If

The above code is used to create a string of product names, ignoring blank cells.

With Range("I12").Validation

'Deleting any previous validation from the cell

.Delete

'Adding the validation

.Add _

Type:=xlValidateList, _

AlertStyle:=xlValidAlertStop, _

Operator:=xlBetween, _

Formula1:=Txt

End With

The above code is used to assign validation to cell I12.

Please follow below for the code

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Declaring variables
Dim IntRow As Integer, IntLastRow As Integer
Dim Txt As String

With Worksheets("Main")
    
    'Finding the row number of last cell
    IntLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
    'Looping from 10th row to Last row
    For IntRow = 10 To IntLastRow
        
        'Creating text for validation list
        If Not IsEmpty(.Cells(IntRow, 1)) Then
        
            'Concatening non blank values in the first column
            Txt = Txt & .Cells(IntRow, 1) & ","
        End If
    Next IntRow
End With

Txt = Left(Txt, Len(Txt) - 1)

'Adding validation to cell I12
With Range("I12").Validation
    'Deleting any previous validation from the cell
    .Delete
    'Adding the validation
    .Add _
    Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, _
    Formula1:=Txt
End With

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com