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


how to insert macros in excelvisual basic comboboxexcel count filled cellsusing hlookup in excelexcel macro waitdatediff excellvba unlock sheetsql query excel spreadsheetleft function in vbaclose userform in excel vbaremove hyperlink excelexcel 2013 find duplicatesddl queries in sql with examplesproject tracking with excelhow to check duplicate values in excelformulas in excel 2007 with examplesprotect structure and windows excelexcel macro to remove duplicatesexcel vlookup offsetvb6 calculatorexcel delete defined namehow to learn vba excelexcel 3d surface plothow to learn excel macrosexcel how to insert a rowms access vba programming tutorialnumber of rows in excel 2003vba with accessexcel 2007 dashboard tutorialexcel worksheet samplesdeclaring array vbafind duplicates in excel 2013ms word vba tutorialdim vba excelcost estimate excel template2d array vbahow to remove password protection in excel 2010consolidate excelrun excel macrovba userform textboxsheets excel vbaexcel macro save as xlsxvba applicationsyntax in excelsample sql queries for interviewhow to use hyperlink function in excelascending and descending order in excelexcel vlookup case sensitivevba in excel examplescountif functionshow to delete specific rows in excelexcel 2013 advanced filterexcel vba cell valueexcel macro tipsdeveloper ribbon excel 2007excel tick boxmicrosoft activex data objects 2.8 librarypivot table sample dataexcel vba array lengthwhat is workbook and worksheetwhat does it mean to merge cellshow to copy cells in excel to another sheetexcel numerical orderif isna excelexcel macro activecellexcel activate macrosvba worksheet nameexcel 2010 chart wizardhow many columns and rows in excel 2007pivotfields vbars open vbatask planner templatelock excel with passwordexcel vba offset