Add Unique Entries Validation List Excel VBA Macro

In a previous post, we have seen how to get a list of unique entries in Excel using VBA macros. Now we are going to add a validation list with available unique entries to the selected range using Excel VBA.

 

Macro/VBA code:

 

  Sub AddValidationToSelection()

      Dim MyCol As New Collection, MyList As String, i As Integer

   

      On Error Resume Next

      For Each elem In Selection

          MyCol.Add elem, elem

      Next

   

      For i = 1 To MyCol.Count

          MyList = MyList & MyCol.Item(i) & ","

      Next

   

      With Selection.Validation

          .Delete

          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

          xlBetween, Formula1:=MyList

          .InCellDropdown = True

      End With

  End Sub

 

 

Macro explained:

• We are going to use a collection to store unique entries in the selection. Thus, we declare MyCol as a new collection. We also declare MyList as a string that will hold the list of unique entries separated by comma. We need an integer variable to keep the index of each element.
• The first loop adds each element in the selection to the new collection MyCol, but only if that exact element has not been added before. Then, we can access each unique element in the collection with MyCol.Item(i), where i is the index assign to each unique element and spans from one to the total number of unique elements (determined by MyCol.Count).
• The second loop adds each unique element to a string (MyList) separated by comma. MyList will be a comma-delimited string with all the unique entries. This is the format we need to add the validation list.
• Finally, we add the validation list to the selected range (although this could be added to other location in the worksheet or in a different sheet altogether). Note that we first delete the existing validation (if any), and then add a validation list.

This is how we add a unique entries validation list in Excel with VBA macros.

Other examples:

• How to Separate Sheets Into Workbooks with Excel VBA macros
  
• How to Add Web Query to Worksheet with Excel VBA macros
  
• How to Get Info of Selected Files with Excel VBA macros