Using Excel VBA, how can i extract data from a named table and manipulate the data without changing the data in the original table?

Question

In Excel VBA, I am trying to extract a unique list of values from a column in an named table. I want to store the unique list of values in a variable that I can then use for further processing. I am using the following code to do this:

    Dim SomeData As Range
     
    Set SomeData = ThisWorkbook.Sheets("Tab") _
    
        .ListObjects("TableName").ListColumns("ColumnName").DataBodyRange  
    
    SomeData.RemoveDuplicates Columns:=1, Header:=xlNo

However, this code removes duplicates from the underlying table - which I don't want to do.

Is there a simple way to extract the data from the table and store it in a variable that I can manipulate without changing the underlying data in the named table?

Answer 1

Add the values to a collection using the cell value as the key - you can't have duplicate keys in a collection.

Public Sub Test()

    On Error GoTo Err_Handler

    Dim MyCol As Collection
    Set MyCol = New Collection
    
    Dim Cell As Range
    For Each Cell In ThisWorkbook.Worksheets("Tab").ListObjects("TableName").ListColumns("ColumnName").DataBodyRange
        MyCol.Add Cell.Value2, Cell
    Next Cell
    
    Debug.Assert False
    
    On Error GoTo 0
    
Exit Sub
Err_Handler:
    Select Case Err.Number
        Case 457 'This key is already associated with an element of this collection
            Resume Next
        Case Else
            MsgBox Err.Number & vbCr & Err.Description, vbOKOnly + vbCritical, "Module1.Test()"
    End Select

End Sub  

Or

You can use Application.Worksheetfunction.Unique:

Public Sub Test()

    Dim MyVar As Variant
    MyVar = Application.WorksheetFunction.Unique(ThisWorkbook.Worksheets("Tab").ListObjects("TableName").ListColumns("ColumnName").DataBodyRange)
    
End Sub