Results 1 to 3 of 3

Extracting unique values from filtered column to array

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-05-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    301

    Extracting unique values from filtered column to array

    Hi all

    I have a need to
    1. Filter a sheet,
    2. Extract unique values from a column, and
    3. Populate a listbox (on another sheet) with the unique values.

    I can filter using VBA, but I don't have the foggiest on how to extract unique values from a filtered column into an array.
    The filter

    rngFilter.AutoFilter Field:=iColumnNumber, Criteria1:=arrSelectedArray, Operator:=xlFilterValues
    With this, the idea is that I can use a listbox(1) to select the items I want to filter. This will filter the sheet. Then I collect the unique values from another column which I can then use to populate listbox(2)

    then listbox(3),(4)... and so on.

    It should be noted that I am using this code to extract unique items, but I don't now how to make it work with a filtered list

    Dim dic As Object, vData As Variant, i As Long
    Dim Array1 As Variant
    'Y is my filtered results column
        
        Set WkSh = Worksheets("MySheet")
        'Create a dictionary object
        Set dic = CreateObject("Scripting.Dictionary")
        
        LRfilt = WkSh.Range("Y" & Rows.Count).End(xlUp).Row
        dic.CompareMode = vbTextCompare
        'Pass the data to a variant array to speed processing
        vData = WkSh.Range("Y2:Y" & LRfilt)
        'Loop
        For i = LBound(vData) To UBound(vData)
            If vData(i, 1) <> "" Then dic(vData(i, 1)) = Empty
        Next i
        'Pass the unique keys to a 0 based array
        Array1 = dic.keys
    
        'Confirm content of array
        For i = 0 To UBound(Array1)
            Debug.Print Array1(i)
        Next i
    I'm sorry I can't share my sheet because that would be pointless, and because it's too big and contains company data.

    Can anybody suggest some code that will help with this?

    Cheers
    Last edited by Journeyman3000; 08-12-2020 at 05:18 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Extracting unique values from a column
    By CDandVinyl in forum Excel Programming / VBA / Macros
    Replies: 43
    Last Post: 10-25-2019, 10:11 PM
  2. [SOLVED] Translating array formula - count unique values between dates on filtered sheet - to VBA
    By L plates in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-10-2018, 09:45 AM
  3. [SOLVED] Dynamically Extracting Unique Values from Filtered List (Visible Rows Only)
    By AliGW in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-08-2018, 10:37 AM
  4. [SOLVED] Formula extracting unique column values
    By labogola in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-10-2013, 02:34 AM
  5. Count unique, visible values in a filtered column
    By kajakk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-07-2013, 08:42 AM
  6. count unique values in filtered column
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-09-2013, 10:40 AM
  7. Count Unique Values in a Filtered Column
    By mashley in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-30-2008, 10:56 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1