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.
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.
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) & “,”
The above code is used to create a string of product names, ignoring blank cells.
‘Deleting any previous validation from the cell
‘Adding the validation
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
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 firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.