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.
Comments
Post a Comment