Excel VBA: Remove Duplicates from an Array (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Many a time while working with VBA in Excel, we need to remove the duplicates from an array. In this article, I’ll show you how you can remove duplicates from an array with proper examples and illustrations.


Remove Duplicates from an Array with Excel VBA (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

VBA Code to Remove Duplicates from an Array in Excel


Remove Duplicates from an Array with Excel VBA: Overview 

Without further delay, let’s go to our main discussion today. Let’s see how we can remove the duplicates from an array with step-by-step analysis.

⧪ Step 1: Declaring the Array

First of all, we have to declare the array. Here we’ve declared 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 Changing Them to an Empty String

Next, we’ll iterate through each element of the array with a for-loop and count the number of duplicate values. We’ll 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 i

⧪ Step 3: Removing the Empty Strings (Places in Place of the Duplicates)

Next, we’ll remove the empty strings that were placed in the place of the duplicate values. Thus the duplicate values from the array will be removed.

We’ll use another for-loop to execute this.

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, you can display the array 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_String

Therefore, the complete VBA code will be:

⧭ VBA Code:

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

VBA Code to Remove Duplicates from an Array in Excel

⧭ Output:

Run the code (Obviously after changing the array according to your need). It’ll display the array after removing all the duplicates from it.

Remove Duplicates from an Array with Excel VBA Output


Remove Duplicates from an Array with Excel VBA: 2 Suitable Examples


1. Develop a Macro to Remove Duplicates from a Range of Cells with Array in Excel VBA

Here we’ve got a data set 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.

Data Set to Remove Duplicates from an Array with Excel VBA

Now we’ll develop a Macro to remove the duplicate values from a range of this data set with VBA.

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 of the worksheet.

⧭ Extra:

I have removed the duplicate names from the Host Countries. Can you remove them from the Champion and the Runners-Up countries? Try yourself.


2. Create a User-Defined Function to Remove Duplicates from a Range of Cells with Array in Excel VBA

Now, we’ll 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

VBA Code to Remove Duplicates from an Array with Excel VBA

⧭ Output:

Select any range of cells in your worksheet and enter the formula:

=Remove_Duplicates(C3:C23)

It’ll remove the duplicates from the host countries and show them in the selected cell.

Read More: How to Remove Duplicate Rows Based on One Column Using Excel VBA


Things to Remember

  • Don’t forget to press CTRL + SHIFT +ENTER unless you are in Office 365.
  • You can repeat the formula for the champion and the runners-up countries.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

So, this is the way to remove the duplicates from an array with VBA in Excel. Do you have any questions? Feel free to ask us.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

4 Comments
  1. What does Count2 do? I can’t see a definition for that variable?

  2. 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.

  3. 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.

      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo