Excel – Ignore blanks and duplicates in Validation list

Posted on by

I faced a problem where I had a worksheet where in column A there were items I wanted to use as source for validation list in another column. But these items were not unique, therefore they appeared in the validation list not just once. So I used the solution presented below to automatically create a unique list in a spare column and assign that list as the source of data validation drop down list for a specified cell range

  • Column A – Source column with duplicates
  • Column B – Filtered column computed by the macro (used as source list for validation)
  • Column C – Validated column against values in column B
Private Sub Worksheet_Change(ByVal Target As Range) 
  Dim i, LastRow, LastRow_B

  Application.ScreenUpdating = False   
  Application.EnableEvents = False 
  Columns("B:B").Hidden = True 
  Columns("B:B").ClearContents 
  LastRow = Application.Cells. SpecialCells(xlCellTypeLastCell).Row 

  For i = 1 To LastRow 
    If Application.CountIf(Range("B:B"), Cells(i, "A").Value) = 0 Then 
      Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Cells(i, "A").Value 
    End If 
  Next 

  LastRow_B = Range("B" & Rows.Count).End(xlUp).Row 

  Range("C:C").Validation.Delete 
  With Range("C:C").Validation 
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ 
    Operator:=xlBetween, Formula1:="=$B$2:$B$" & LastRow_B
    .IgnoreBlank = True 
    .InCellDropdown = True 
    .InputTitle = "" 
    .ErrorTitle = "" 
    .InputMessage = "" 
    .ErrorMessage = "" 
    .ShowInput = True 
    .ShowError = True 
  End With 

  Application.EnableEvents = True 
End Sub

Leave a Reply