Excel VBA to Get Unique Values from Column (4 Examples)

We will use the following dataset to get unique values from a column. The dataset contains some products in column B and the country where the products will be delivered to in column C. We want to get the unique countries from column C to column E.

Method 1 – Find Exclusive Values from Columns with Excel VBA

Let’s extract the unique values from column C and put those values in column E using Excel VBA Macros.

Steps:

• Fo to the Developer tab from the ribbon.
• Click on Visual Basic to open the Visual Basic Editor. Or, press Alt + F11 to open the Visual Basic Editor.

• You can also just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.

• Click on Module from the Insert drop-down menu bar. This will create a Module in your workbook.

• Copy and paste the VBA code shown below:

VBA Code:

Sub Get_Unique_Values1()
Dim row As Long
row = Cells(Rows.Count, "C").End(xlUp).row
ActiveSheet.Range("C5:C" & row).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("E5"), _
Unique:=True
End Sub

• Run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

• You’ll get the unique countries from the delivery country column.

Sub Get_Unique_Values1()

Dim row As Long

row = Cells(Rows.Count, "C").End(xlUp).row

ActiveSheet.Range("C5:C" & row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("E5"), Unique:=True

End Sub

Method 2 – Excel VBA to Get Unique Values

Steps:

• Open the Visual Basic Editor with Alt + F11.
• Go to Insert and select Module from the drop-down menu. This will open up the code module.
• Copy the following VBA code there.

VBA Code:

Sub Get_Unique_Values2()
Set myRng = Range("C5:C14")
Set r = Range("E5")
myRng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=myRng, CopyToRange:=r, Unique:=True
End Sub

• Press the F5 key or click on the Run Sub button to run the code.

• You will get the unique values as shown in the first method.

Set myRng = Range("C5:C14")
Set r = Range("E5")

myRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=myRng, CopyToRange:=r, Unique:=True

Method 3 – Extract Unique Values from Columns Using VBA in Excel

Steps:

• Open the Visual Basic Editor with Alt + F11.
• Go to Insert and select Module from the drop-down menu. This will open up the code module.
• Copy the following VBA code there.

VBA Code:

Sub Get_Unique_Values3()
Dim myArr As Variant
Dim rowC As Long
With Sheet9
Sheets("Example3").Columns("C:C").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("E2"), Unique:=True
rowC = .Cells(.Rows.Count, "C").End(xlUp).row
myArr = .Range("C3:C" & row)
End With
Dim myVal As String
Dim a As Integer
For a = 1 To UBound(myArr)
myVal = myVal & myArr(a, 1) & ","
Next
End Sub

• Press the F5 key to run the code or click on the Run Sub button.

• You’ll get the unique values from the column shown in the first method.

Dim myArr As Variant
Dim rowC As Long

With Sheet9

Sheets("Example3").Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("E2"), Unique:=True

rowC = .Cells(.Rows.Count, "C").End(xlUp).row   
myArr = .Range("C3:C" & row)

Dim myVal As String   
Dim a As Integer

For a = 1 To UBound(myArr)        
myVal = myVal & myArr(a, 1) & ","  
Next

Method 4 – Run a VBA Macro to Extract Unique Values from a Column into a MsgBox

Steps:

• Open the Visual Basic Editor with Alt + F11.
• Go to Insert and select Module from the drop-down menu. This will open up the code module.
• Copy the following VBA code there.

VBA Code:

Sub Get_Unique_Values4()
mySheet = Sheets("Example4").Range("C5:C14")
With CreateObject("scripting.dictionary")
For Each myData In mySheet
a = .Item(myData)
Next
MsgBox Join(.keys, vbLf)
End With
End Sub

• Run the code by pressing F5 on your keyboard.

• You see the result in a Msgbox.

Download Practice Workbook

You can download the workbook and use it for practice.

<< Go Back to Learn Excel