VBA Remove Duplicates from Array

VBA to Remove Duplicates from Array Excel Macros Examples Codes: to delete duplicate records from an array in MS Excel 2003, 2007, 2010, 2013. We will also see the practical example for deleting the duplicates from an array.

VBA code to Remove Duplicates from Array

Here is the Example VBA syntax and Example VBA Macro code to Remove Duplicates from an Array in Excel. This will help you to know how to delete duplicate records from an array using VBA.

VBA Remove Duplicates from Array: Procedure

Following is the VBA Syntax and sample VBA macro command to delete duplicate from Array using VBA. In this method we will first get the data to Excel worksheet and remove the duplicate using RemoveDuplicates method and reload into the Array.

VBA Remove Duplicates from Array: Examples

The following Excel VBA macro code is to delete duplicate from an array. This VBA macro will create a temporary sheet. And enter the data from array to temporary worksheet. Then remove the duplicate records. And again reload the unique items into array. Finally it deletes the temporary worksheet.

Sub sbRemove_Duplicates_From_Array()
Dim iCntr As Long
Dim recCountBefore As Long
Dim recCountAfter As Long
Dim lRow As Long
Dim arrTargetArray As Variant

arrTargetArray = Array(1, 2, 3, 4, 5, 3, 2, 6, 7, 5, 3, 3)
recCountBefore = UBound(arrTargetArray, 1)

Set tmpSht = ThisWorkbook.Worksheets.Add

For iCntr = 0 To UBound(arrTargetArray, 1)
    tmpSht.Cells(iCntr + 1, 1) = arrTargetArray(iCntr)
Next

tmpSht.Columns(1).RemoveDuplicates Columns:=Array(1)
lRow = tmpSht.Range("A60000").End(xlUp).Row
ReDim arrTargetArray(lRow - 1)
For iCntr = 0 To UBound(arrTargetArray, 1)
    arrTargetArray(iCntr) = tmpSht.Cells(iCntr + 1, 1)
Next

Application.DisplayAlerts = False
tmpSht.Delete
Application.DisplayAlerts = True

recCountAfter = UBound(arrTargetArray, 1)

MsgBox "Actual Items in the Array: " & recCountBefore & vbCr _
& "Unique Items in the Array: " & recCountAfter & vbCr _
& "Items Removed from the Array: " & recCountBefore - recCountAfter, vbInformation
End Sub 

Instructions to run the VBA Macro code to delete duplicates from Array

Please follow the below steps to execute the VBA code to delete duplicates from array.

• Step 1: Open any Excel workbook
• Step 2: Press Alt+F11 – This will open the VBA Editor
• Step 3: Insert a code module from then insert menu
• Step 4: Copy the above code and paste in the code module which have inserted in the above step
• Step 5: Now press F5 to execute the code

Now you can observe that the duplicate items are deleted from the array. You can debug the code using F8 and see the Final array using Watch window in VBA Editor.

Explained VBA Code to Delete Duplicates from an array

Starting Macro program and sub procedure to write VBA code to delete duplicate records from array.

Sub sbRemove_Duplicates_From_Array()
‘Declaring variables
‘iCntr to loop through the array.
Dim iCntr As Long
‘Variable for capturing the number of items in the array initally.
Dim recCountBefore As Long
‘Variable for capturing the number of unique items in the array after removing the duplicates.
Dim recCountAfter As Long

‘lRow to store the last Row in the worksheet.
Dim lRow As Long
‘arrTargetArray is our Arry for testing purpose.
Dim arrTargetArray As Variant

‘assigning some sample data into arry for testing purpose.
arrTargetArray = Array(1, 2, 3, 4, 5, 3, 2, 6, 7, 5, 3, 3)

‘Getting the number of items beore removing the items from array.
recCountBefore = UBound(arrTargetArray, 1) + 1

‘Adding a temporary worksheet.
Set tmpSht = ThisWorkbook.Worksheets.Add
‘Getting the values from array to temporary worksheet.
For iCntr = 0 To UBound(arrTargetArray, 1)
tmpSht.Cells(iCntr + 1, 1) = arrTargetArray(iCntr)
Next

‘Removing the duplicates in the worksheets.
tmpSht.Columns(1).RemoveDuplicates Columns:=Array(1)

‘finding last row after deleting the duplicates.
lRow = tmpSht.Range(“A60000”).End(xlUp).Row

‘Resizing the array.
ReDim arrTargetArray(lRow – 1)

‘Reloading the unique items from temporary sheet to arry.
For iCntr = 0 To UBound(arrTargetArray, 1)
arrTargetArray(iCntr) = tmpSht.Cells(iCntr + 1, 1)
Next

‘Stopping alerts and delting worksheet
Application.DisplayAlerts = False
tmpSht.Delete
Application.DisplayAlerts = True

‘Getting the unique records vount.
recCountAfter = UBound(arrTargetArray, 1) + 1

‘displaying the message.
MsgBox “Actual Items in the Array: ” & recCountBefore & vbCr _
& “Unique Items in the Array: ” & recCountAfter & vbCr _
& “Items Removed from the Array: ” & recCountBefore – recCountAfter, vbInformation

End Sub
End statement to end the VBA code to delete duplicate records from array.