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.

ArrowMain

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

ArrowRawData

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



3 thoughts on “Validation list ignoring empty cells using VBA in Microsoft Excel

  1. “Congartualtion great solution.
    I tried this to erase all emty fields within my validation. Unfortunately I do have many different fields to select from and at all about 400 characters in the validation field.
    In this case the macro runs in an error.What do you recommend in that case?”

  2. “Validation format cannot be used on cells when sheets are in group mode (severeal sheets selected). A workaround could be as follows:
    (put before test on target cell) If ActiveWindow.SelectedSheets.Count > 1 Then Exit Sub
    The correct procedure to apply to all sheets in workbook is: Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) The code is otherwise correct”

  3. “Hi
    Wondering if anyone can help?
    I have a spreadsheet with 4 columns, I want to make Excel force an input into a notes field when someone tries to edit some of the information in the a cell.
    Is this possible?
    I.e if someone changes the invoice details in one cell, they must input a reason and date into a different cell, with Excel bringing up a message box.
    Really appreciate your expertise
    Regards”

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>