Quicker way to get all unique values of a column in VBA?

by

Use Excel’s AdvancedFilter function to do this.

Using Excels inbuilt C++ is the fastest way with smaller datasets, using the dictionary is faster for larger datasets. For example:

Copy values in Column A and insert the unique values in column B:

Range("A1:A6").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1"), Unique:=True

It works with multiple columns too:

Range("A1:B4").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("D1:E1"), Unique:=True

Be careful with multiple columns as it doesn’t always work as expected. In those cases I resort to removing duplicates which works by choosing a selection of columns to base uniqueness. Ref: MSDN – Find and remove duplicates

enter image description here

Here I remove duplicate columns based on the third column:

Range("A1:C4").RemoveDuplicates Columns:=3, Header:=xlNo

Here I remove duplicate columns based on the second and third column:

Range("A1:C4").RemoveDuplicates Columns:=Array(2, 3), Header:=xlNo

More Related Contents:

  1. SumIf using Excel VBA
  2. How can I call a macro which contains a loop but only run one iteration of that loop?
  3. Test or check if sheet exists
  4. How can I find last row that contains data in a specific column?
  5. Copy from one workbook and paste into another
  6. How to copy Outlook mail message into excel using VBA or Macros
  7. Open Excel file for reading with VBA without display
  8. For Loop not fully cycling in excel VBA [duplicate]
  9. Split cell values into multiple rows and keep other data
  10. Difference between ‘on error goto 0’ and ‘on error goto -1’ — VBA
  11. Is VBA an OOP language, and does it support polymorphism?
  12. Export each sheet to a separate csv file
  13. What are .NumberFormat Options In Excel VBA?
  14. Excel: How to check if a cell is empty with VBA? [duplicate]
  15. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  16. Extract Data from Word Document to an Excel SpreadSheet
  17. Excel macro -Split comma separated entries to new rows [duplicate]
  18. VBA equivalent to Excel’s mod function
  19. Get text from clipboard using GetText – avoid error on empty clipboard
  20. Display milliseconds in Excel
  21. VBA editor auto-deletes spaces at the ends of lines
  22. Select entire column in table using Excel VBA
  23. Application.Quit command not closing the entire Excel Application
  24. Reducing WithEvent declarations and subs with VBA and ActiveX
  25. Trying to open the workbook in separate instance
  26. Application.WorksheetFunction.Match method
  27. Excel Hide/Show all tabs on Ribbon except custom tab
  28. Macros Not Showing Up in Macro Table
  29. Excel telling me my blank cells aren’t blank
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment