In this article, we’ll demonstrate how to remove duplicates from an array using Excel VBA code.
VBA Code Quick View
Sub Remove_Duplicates_from_Array()
Dim MyArray() As Variant
MyArray = Array("A", "B", "C", "B", "B", "D", "C", "E", "F", "C", "B", "G")
Count = 0
For i = LBound(MyArray) To UBound(MyArray) - Count
For j = LBound(MyArray) To UBound(MyArray) - Count2
If i <> j And MyArray(i) = MyArray(j) And MyArray(i) <> "" Then
MyArray(j) = ""
Count = Count + 1
End If
Next j
Next i
For i = LBound(MyArray) To UBound(MyArray)
If MyArray(i) = "" Then
For j = i To UBound(MyArray) - 1
MyArray(j) = MyArray(j + 1)
Next j
If i < UBound(MyArray) - Count + 1 Then
i = i - 1
End If
End If
Next i
ReDim Preserve MyArray(UBound(MyArray) - Count)
Array_String = ""
For i = LBound(MyArray) To UBound(MyArray)
Array_String = Array_String + MyArray(i) + " "
Next i
MsgBox Array_String
End Sub
Overview
Step 1 – Declaring the Array
First of all, we have to declare the array. We declare it as MyArray, which contains a few duplicates.
Dim MyArray() As Variant
MyArray = Array("A", "B", "C", "B", "B", "D", "C", "E", "F", "C", "B", "G")Step 2 – Counting the Number of Duplicates and Converting Them to Empty Strings
We iterate through each element of the array with a For loop and count the number of duplicate values. We exchange each duplicate value with an empty string.
Count = 0
For i = LBound(MyArray) To UBound(MyArray) - Count
For j = LBound(MyArray) To UBound(MyArray)
If i <> j And MyArray(i) = MyArray(j) And MyArray(i) <> "" Then
MyArray(j) = ""
Count = Count + 1
End If
Next j
Next iStep 3 – Removing the Empty Strings
We use another For loop to remove the empty strings that replaced the duplicate values. Thus the duplicate values from the array are completely removed.
For i = LBound(MyArray) To UBound(MyArray)
If MyArray(i) = "" Then
For j = i To UBound(MyArray) - 1
MyArray(j) = MyArray(j + 1)
Next j
If i < UBound(MyArray) - Count + 1 Then
i = i - 1
End If
End If
Next i
ReDim Preserve MyArray(UBound(MyArray) - Count)Step 4 (Optional) – Displaying the Array after Removing the Duplicates
The duplicates from the array have been removed. Now if you wish, the array can be displayed by converting it into a string separated by spaces.
Array_String = ""
For i = LBound(MyArray) To UBound(MyArray)
Array_String = Array_String + MyArray(i) + " "
Next i
MsgBox Array_StringThe complete VBA code is:
Sub Remove_Duplicates_from_Array()
Dim MyArray() As Variant
MyArray = Array("A", "B", "C", "B", "B", "D", "C", "E", "F", "C", "B", "G")
Count = 0
For i = LBound(MyArray) To UBound(MyArray) - Count
For j = LBound(MyArray) To UBound(MyArray)
If i <> j And MyArray(i) = MyArray(j) And MyArray(i) <> "" Then
MyArray(j) = ""
Count = Count + 1
End If
Next j
Next i
For i = LBound(MyArray) To UBound(MyArray)
If MyArray(i) = "" Then
For j = i To UBound(MyArray) - 1
MyArray(j) = MyArray(j + 1)
Next j
If i < UBound(MyArray) - Count + 1 Then
i = i - 1
End If
End If
Next i
ReDim Preserve MyArray(UBound(MyArray) - Count)
Array_String = ""
For i = LBound(MyArray) To UBound(MyArray)
Array_String = Array_String + MyArray(i) + " "
Next i
MsgBox Array_String
End Sub
Output:
Run the code (after modifying the array according to your need).
A message box will display the array after all the duplicates have been removed from it.
Remove Duplicates from an Array: 2 Suitable Examples
Example 1 – Using a Macro
Here we have a dataset extending over the range B2:E23 that contains the Year, Host Country, Champion, and Runners-Up of all the FIFA World Cups that took place from 1930 to 2018.
Let’s remove the duplicate values from the Host Countries and put them in cell G3.
The VBA code will be:
VBA Code:
Sub Remove_Duplicates_from_Range()
Set Rng = Range("C3:C23")
Dim MyArray() As Variant
ReDim MyArray(Rng.Rows.Count - 1)
For i = LBound(MyArray) To UBound(MyArray)
MyArray(i) = Rng.Cells(i + 1, 1)
Next i
Count = 0
For i = LBound(MyArray) To UBound(MyArray) - Count
For j = LBound(MyArray) To UBound(MyArray)
If i <> j And MyArray(i) = MyArray(j) And MyArray(i) <> "" Then
MyArray(j) = ""
Count = Count + 1
End If
Next j
Next i
For i = LBound(MyArray) To UBound(MyArray)
If MyArray(i) = "" Then
For j = i To UBound(MyArray) - 1
MyArray(j) = MyArray(j + 1)
Next j
If i < UBound(MyArray) - Count + 1 Then
i = i - 1
End If
End If
Next i
ReDim Preserve MyArray(UBound(MyArray) - Count)
For i = LBound(MyArray) To UBound(MyArray)
Range("G3").Cells(i + 1, 1) = MyArray(i)
Next i
End Sub
Output:
Run the code. It’ll remove the duplicate names from the Host Countries and put them in cell G3.
Extra:
Can you remove duplicates from the Champion and Runners-Up countries? Try it yourself.
Example 2 – Using a User-Defined Function
We can also develop a User-Defined function to remove the duplicate values from a range of cells.
The VBA code will be:
VBA Code:
Function Remove_Duplicates(Rng As Range)
Dim MyArray() As Variant
ReDim MyArray(Rng.Rows.Count - 1)
For i = LBound(MyArray) To UBound(MyArray)
MyArray(i) = Rng.Cells(i + 1, 1)
Next i
Count = 0
For i = LBound(MyArray) To UBound(MyArray) - Count
For j = LBound(MyArray) To UBound(MyArray)
If i <> j And MyArray(i) = MyArray(j) And MyArray(i) <> "" Then
MyArray(j) = ""
Count = Count + 1
End If
Next j
Next i
For i = LBound(MyArray) To UBound(MyArray)
If MyArray(i) = "" Then
For j = i To UBound(MyArray) - 1
MyArray(j) = MyArray(j + 1)
Next j
If i < UBound(MyArray) - Count + 1 Then
i = i - 1
End If
End If
Next i
ReDim Preserve MyArray(UBound(MyArray) - Count)
Remove_Duplicates = MyArray
End Function
Output:
Select any blank cell in your worksheet and enter the formula:
=Remove_Duplicates(C3:C23)Press Enter (or CTRL + SHIFT +ENTER if you are not using Office365) to remove the duplicates from the Host Countries and show them in the selected cell.
Download Practice Workbook
Related Articles
- How to Delete Duplicate Rows in Excel with VBA
- Excel VBA: Remove Duplicates Comparing Multiple Columns
What does Count2 do? I can’t see a definition for that variable?
Hi VBANEWB, thanks for reaching out. Here, we declared Count variable first. So the VBA automatically accepts Count2 as a similar variable. So we didn’t need to declare it separately.
Nahian – you answered VBANEWB’s second question, regarding not needing to define it. But what is it actually doing in the code? I you do not assign it a value anywhere. You also do not ever change it’s value anywhere. It just seemingly is an empty variable not doing anything? I don’t understand it’s use…
Hello Rusty, thanks for reaching out. Your idea is correct. We don’t need to use the count2 variable here. Maybe it’s inserted to maintain the similarity with the previous line as it worked properly. Here is the updated code without the count2 variable. Hope this removes the confusion.