VBA Remove Duplicates from Array

Home/VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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.

LIMITED TIME OFFER
By |January 19th, 2015|VBA|0 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

Leave A Comment


Related pages


dynamic array vba excelvba syntax errorusing lookup in excelprogress bar vba excelauto fit row heightvba refresh all pivot tablesexcel color index valuesdynamic graph excelunlocking protected excel sheetvba workbook objectvlookup example dataexcel automatic sorthow to delete a cell in excelexcel vba clipboardms excel developer tabexcel vba range addressexcel functions in vbapassword sheet excelhow to merge columns excelmacro for merging excel filesexcel vba sample projectshow do i copy cells in excelhow to use pivot chart in excel 2007common excel commandscreate macro button excel 2010excel enable macroexcel vba formula with quotesinsert macro in excelrun sub in vbasimple excel macros examplesexample sumif excelunprotect excel vba codemacro to unlock excelproject implementation plan template excelexcel vba range arrayexcel project plan ganttmsgbox function in vbavba export to csvmicrosoft excel how to merge cellsword vba selectionremove duplicate excel 2003vba autofitcheck excel for duplicateslookup function in excel exampleconcatenate excel 2007protect excel spreadsheetunprotect cells in excelvlookup returns 0powerpoint macro tutorialexcel project gantt templatevbscript carriage returnvba nullvba paste from clipboardsql commands pptprofessional excel development ebookconvert excel column width to inchesvba excel activecelldrop down menu vbaexcel match function examplefor each in vba excelvba select case multiple conditionsvba create querycreate excel drop down list 2007vba drop down listexcel macro learningunprotect a worksheetvba meaningvba worksheet cellsimportant interview questions in sqlscrum excel templatevba code to open excel file