List Unique Entries Excel VBA Macro

We can easily filter data within columns in Excel. Sometimes we need however to get the list of those unique entries Excel is showing to filter through, so here’s a macro that gets that list for us. Use the code below to get the list of unique entries with Excel VBA macros.

Macro/VBA code:

  

  Sub ListUniqueEntries()

      Dim ws1 As Worksheet, ws2 As Worksheet

      Dim rng1 As Range, rng2 As Range

      Set ws1 = Sheets("Sheet1")

      Set ws2 = Sheets("Sheet2")

      Set rng1 = ws1.Range("A:A")

      Set rng2 = ws2.Range("A1")

      If WorksheetFunction.CountA(rng1) > 0 Then

          rng1.AdvancedFilter Action:=xlFilterCopy, copytorange:=rng2, _

          unique:=True

      Else

          MsgBox "No Entries in Column A"

      End If

  End Sub

Macro explained:

• First we declare object variables for Worksheet objects (ws1, ws2) and Range objects (rng1, rng2).
• Then we define the object variables with the Set statement. The number 1 is used for the target sheet (ws1) and range (rng1), while the number 2 refers to the sheet (ws2) and range (rng2) where we will copy the list of unique entries. In this example, this target range is the entire column A.
• Next we use the CountA worksheet function to check if there is data in column A (rng1). WorksheetFunction.CountA returns the count of cells with a value.
• If column A is not empty, we apply the AdvancedFilter method of the target range (rng1) to copy the filtered data or unique entries into the recipient range (rng2) in other sheet (ws2).
• If column A is empty a message box will prompt with that information.

This is how we list unique entries in Excel with VBA macros.

Other examples:

• How to Delete Blank Rows with Excel VBA macros
• How to Highlight Duplicates with Excel VBA macros
• How to Order Sheets Alphabetically with Excel VBA macros