Remove duplicates from array using VBA

by

I answered a similar question. Here is the code I used:

Dim dict As Object
Dim rowCount As Long
Dim strVal As String

Set dict = CreateObject("Scripting.Dictionary")

rowCount = Sheet1.Range("A1").CurrentRegion.Rows.Count

'you can change the loop condition to iterate through the array rows instead
Do While rowCount > 1
  strVal = Sheet1.Cells(rowCount, 1).Value2

  If dict.exists(strVal) Then
    Sheet1.Rows(rowCount).EntireRow.Delete
  Else
    'if doing this with an array, then add code in the Else block
    ' to assign values from this row to the array of unique values
    dict.Add strVal, 0
  End If

  rowCount = rowCount - 1
Loop

Set dict = Nothing

If you want to use an array, then loop through the elements with the same conditional (if/else) statements. If the item doesn’t exist in the dictionary, then you can add it to the dictionary and add the row values to another array.

Honestly, I think the most efficient way is to adapt code you’d get from the macro recorder. You can perform the above function in one line:

    Sheet1.UsedRange.RemoveDuplicates Columns:=3, Header:=xlYes

More Related Contents:

  1. Save excel file multiple times with file names in another workbook.
  2. Read/Parse text file line by line in VBA
  3. How do I find the last column with data?
  4. Excel VBA: Answer gets “stuck”
  5. How to insert a picture into Excel at a specified cell position with VBA
  6. Save multiple sheets to .pdf
  7. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  8. filter out multiple criteria using excel vba
  9. Excel VBA autofilter all but three
  10. How can I create a calendar input in VBA Excel?
  11. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  12. Excel VBA code to copy a specific string to clipboard
  13. Merge values of column B based on common values on column A
  14. How to import a table from web page (with “div class”) to excel?
  15. VBA: Convert Text to Number
  16. Concatenate cell data into another data if values matches
  17. How can I run VBA code each time a cell gets its value changed by a formula?
  18. How to convert 32 bit VBA code into 64 bit VBA code
  19. Referencing Excel Userform Control Name Value From String (VBA)
  20. Outputting Excel rows to a series of text files
  21. Refreshing all the pivot tables in my excel workbook with a macro
  22. Countif With Multiple OR Criteria
  23. Vlookup to copy color of a cell
  24. every possible combination of the contents of 2 columns in excel
  25. How to delete rows in Excel based on criteria using VBA?
  26. Macro to export MS Word tables to Excel sheets
  27. VBA WinHTTP to download file from password proteced https website
  28. Create folder path if does not exist (saving from VBA)
  29. Concatenate multiple ranges using vba
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment