Hello there,
I am using a couple of combo boxes/validation cells with lists of criteria to filter (autofilter) a table on another sheet on the same excel 2010 workbook. I use VBA code to import the data table from csv file that may contain upto 100,000 rows and hundreds of columns. The list I use on validation cells and combo boxes are Dynamic Named Ranges. I use worksheet change event to trigger the macro when validation cells are changed. Now I need some quicker method to auto update the dynamic named ranges used in validations cells or combo boxes with the list of available criteria on drop down (i.e. the list we see on autofilter columns when we select the dropdown arrow - NOT applied criteria) as soon as I apply the filter on other columns of the table using validation cells/combo boxes. I already use a method (VBA code) which releases the autofilters on each of the columns whichever I use on validation cells & combo boxes one by one, copy the visible cells on the column to another sheet where I have dynamic named ranges, remove the duplicate on this list (using excel remove duplicate method) and reapply the criteria on autofilter. Although I use all the possible methods I know to improve response time (like turning off screen updating & manual mode calculation when macro runs), yet this method is not very quicker when there is too much of records on the table. I searched on different forums but didn't find any method I need. I really appreciate if someone could tell me if there is some easier method to retrieve the available criteria on autofilter column on to a variable (array) or to worksheet cells.
Thanks a lot in advance
Sandheep
Bookmarks