Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Excel VBA Advanced Find Unique Values from Table to separate worksheet
Public ws_unique As Worksheet
Public ws_copy_to As Worksheet
Public ws_table1 As Worksheet
Public lr_table1 As Long
Public lr_unique As Long
Sub init()
Set ws_unique = Sheets("unique")
Set ws_copy_to = Sheets("copy_to")
Set ws_table1 = Sheets("table1")
lr_table1 = Range("A" & Rows.Count).End(xlUp).Row
End Sub
Sub select_table_clear_filters_and_clear_unique()
ws_unique.Cells.Clear
ws_table1.Select
With ActiveSheet
.AutoFilterMode = False
.Range("table1[[#Headers]]").AutoFilter
End With
End Sub
Sub unqiue_values_to_worksheet()
'this assumes you have started with a workbook with three worksheets named "table1", "unique" and and "copy_to"
'the worksheet named table has a table of data that has been formatted as an Excel table and named 'table1'
call init.init
ws_copy_to.Range("A2:Z1000000").Clear
Application.Calculation = xlCalculationManual
Call select_table1_clear_filters_and_clear_unique
Range("A1:A" & lr_table1).Copy
ws_unique.Select
ws_unique.Range("A1").PasteSpecial
ws_unique.Range("A1:A" & lr_table1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws_copy_to.Range("A2"), UNIQUE:=True
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment