vba - how to get the unselected criteria in AutoFilter Excel -

using excel 2007 vba have table. user has filtered column 5 items. know can selected criteria using:

'set table dim lostats listobject set lostats = ws.listobjects("tablestats")  'get filter list dim af5 variant af5 = lostats.autofilter.filters(5).criteria1  'print dim x integer x = lbound(af5) ubound(af5)     debug.print af5(x) next 

but when user has cleared filter column 5. doesn't work after user has cleared filter in column 5

af5 = lostats.autofilter.filters(5).criteria1 

how list of possible criteria user sees in dropdown box?

so, start?

firstly, falsely assume result array. if choose 1 value, lbound(af5) return type mismatch error. can correct with

if isarray(arr)     = lbound(arr) ubound(arr)         debug.print arr(i)     next else     debug.print arr end if          

unfortunately, doesn't solve anything. procedure prints out not filtered items, criteria chosen user. misleading.

consider table 2 columns. when user specifies criteria >3 result see >3. tell values filtered? don't know how many (if any) chosen , ones are. example, second column store {1, 2, 3}, {3, 7, 99}.

this way it:

sub printoutfiltereddata()     dim tbl listobject     dim cellcount long     dim cell range     dim arr() variant     dim long      set tbl = activesheet.listobjects("table1")      on error resume next     cellcount = tbl.databodyrange.columns(2).specialcells(xlcelltypevisible).cells.count     if err.number = 1004         debug.print "all data filtered out"         exit sub     end if      each cell in tbl.databodyrange.columns(2).specialcells(xlcelltypevisible)         if isempty(arr(0))             redim arr(0 0)             arr(0) = cell.value         else             redim preserve arr(0 ubound(arr) + 1)             arr(ubound(arr)) = cell.value         end if     next cell      = lbound(arr) ubound(arr)         debug.print arr(i)     next end sub 

the use of on error resume next prevents procedure throwing error when there no cells left visible, prints out message , exits procedure.