asked on

Excel VBA 2010 - how to create a single dimension with unique values array from table column

I have a workbook that has a defined table on a sheet. I want to get the unique values from one of the columns in the table and put those unique values in an array so I can loop over them. A blank value should count as a unique value. How can this be accomplished? My table name is T_201311 and the column name is EnterpriseID.

byundt

The easiest way to get a list of unique values is using Advanced Filter on that column and writing the unique results to a worksheet range. Here is a code snippet that uses that method to put the unique values in an empty column to the right of the used range. As written, it hides that column and creates a named range that points to the list of unique items (excluding header).

    Set rg = .UsedRange
    Set rg = rg.Cells(1, 1).Offset(0, rg.Columns.Count + 1)
    .Range("A6").CurrentRegion.Columns(1).AdvancedFilter xlFilterCopy, , CopyToRange:=rg, Unique:=True
    Set rg = rg.CurrentRegion
    Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1)
    rg.EntireColumn.Hidden = True

Open in new window

Another way to do it is to create a dictionary object, which doesn't tolerate duplicates.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mamuscia

ASKER

thanks. this works and gives me the array I wanted.