+ Reply to Thread
Results 1 to 6 of 6

VBA get unique visible items from table column and generate drop-down entries

  1. #1
    Registered User
    Join Date
    09-02-2011
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    91

    Question VBA get unique visible items from table column and generate drop-down entries

    I have managed to create sub which is getting the visible contents of filtered column in a table, lets say Table1
    then remove the duplicate entries
    then from the unique list generate drop-down list in another sheet

    the issue is that when the visible entries in the column doesn't contain duplicates i get error (relevant to red line below in code)
    Please Login or Register  to view this content.
    i have commented (in the code) the lines where i was testing solution for unique list

    any ideas?


    Please Login or Register  to view this content.
    Truth fears no questions.

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: VBA get unique visible items from table column and generate drop-down entries

    Ad 1) Without an example (xls file), it is difficult to consider.

    Ad 2) Dictionary
    If there are no repetitions, then 'vData' is not of 'Array' type, then there is no 'LBound(vData)' and 'UBound(vData)', then an error will appear (if the 'vData' variable type is not recognized).

    Ad 3a) Type of variable 'vData' ====> 'If Application.IsText(vData)'
    If to the variable 'vData' ('Variant' type) is assigned something that can be converted to a number, then 'vData' will never be 'Text'. To test the type of this variable, use 'TypeName' or 'VarType'.

    Ad 3b) Values ​​of the 'vData' variable ====> '.ListColumns(4).DataBodyRange.SpecialCells(xlCellTypeVisible)'
    If discontinuous ranges appear when filtering the list, not all data will be saved in the variable 'vData'. Use, for example, 'Areas' (Collection) to enter all data into the variable.

    Ad 4) Range("TaskNumber")
    If "TaskNumber" is a name defined only at the sheet level, an error will occur when referring to it from another sheet. In order for the name to be visible in the whole workbook, it should be declared for the whole workbook's scope.

    Ad 5) ActiveWorkbook.Sheets("MASterMind Critical Data").ListObjects("T_MMData").ListColumns(4).CurrentRegion
    In this form it will not work.
    Last edited by mjr veverka; 04-08-2019 at 10:55 AM.

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: VBA get unique visible items from table column and generate drop-down entries

    @godlev
    In the future please post in the right forum. I moved the thread for you this time. Thx

  4. #4
    Registered User
    Join Date
    09-02-2011
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    91

    Re: VBA get unique visible items from table column and generate drop-down entries

    @porucha vevrku - thank you for your extensive description of possible causes

    1) i have attached example file as advised
    2) Dictionary - i was thinking a way to make On error or IF that will skip if there are no duplicates? can you help me with this one?
    3) istext was replaced isarray - my idea was to predict the situation where filtered table contains only 1 row/entry
    4) TaskNumber is defined on workbook level
    5) i dont know how to answer this one




    @Pepe Le Mokko - i apologize and thank you, will do from now on.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: VBA get unique visible items from table column and generate drop-down entries

    Instead of your current 4 macros ('dropdownCountry', 'dropdownInstance', 'dropdownErrorMessage', 'dropdownTaskNumber') try to use one in this shape/form (adapt them to your needs):
    Please Login or Register  to view this content.
    Calling from another part of the code:
    Please Login or Register  to view this content.
    Placing this code in 'ThisWorkbook' module will call the macro when opening the workbook and will fill the 'Country' cell with the countries:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-02-2011
    Location
    Bulgaria
    MS-Off Ver
    2013
    Posts
    91

    Re: VBA get unique visible items from table column and generate drop-down entries

    wow that's another level of coding - thank you for this flawless solution!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Generate unique list of values based on condition in another table column with VBA
    By dabasir in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-16-2019, 09:12 PM
  2. Replies: 1
    Last Post: 12-20-2016, 10:36 PM
  3. [SOLVED] Count Unique Items in a Column Based on Unique Items in Two Other Columns
    By HangMan in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-31-2015, 04:48 PM
  4. How Do You Count Unique Cell Entries In A Column With Many Rows Without Using Table Array
    By OffInParametricLand in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2014, 04:20 PM
  5. Replies: 7
    Last Post: 05-11-2012, 02:00 PM
  6. increase drop down list visible entries?
    By hydra in forum Excel General
    Replies: 3
    Last Post: 11-21-2010, 02:46 PM
  7. Extract unique items from a Table and place in a column
    By Evilshrew in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-28-2007, 02:36 AM

Tags for this Thread

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