i wondering simple solution reversing filtered values. although seems me easy task, have not had success while researching on internet.
the situation , problem: have table multiple columns, , lot of rows (exact amount not matter obviously) , want see not filtered in 1 column. problem need lot of clicking
for example - in database of projects have filtered ones worth on 500 000 €, mine , coming specific country. 1 click see ones below 500 000 €) still mine , coming specific country.
possible solutions came mind:
- create unique list of filtered, unfiltered, , create unique list of full column, advancefilter difference. (that's white horse - might work in opinion)
- go through each filtering options , check/uncheck 1 one.
- screenshot filters, transfer text, create unique values @ column, invert filter in advanced filtering (very crazy idea, came out of desperation)
- somewhere take list of what's filtered , inverted easy function (that initial thought not working!)
does has idea how approach situation? i able try vba on own happy if can point me in right direction. of course welcome thoughts in code too.
here's idea toggle numeric filter. won't work numeric filters, of them. instance, won't work between, because uses criteria1 , criteria2. expand code account that.
also, works on numeric filters. work on text filters, if 1 criteria applied.
sub invertnumericfilter() dim lfilter long dim lo listobject dim fltr filter dim aoper variant, aopp variant dim long 'aopp opposite of corresponding 'operator in aoper aoper = split("<> <= >= = < >") aopp = split("= > < <> >= <=") 'find column you're in set lo = activecell.listobject lfilter = activecell.column - lo.databodyrange.column + 1 set fltr = lo.autofilter.filters(lfilter) 'if first characters of criteria in aoper 'then swap them aopp = lbound(aoper) ubound(aoper) if left(fltr.criteria1, len(aoper(i))) = aoper(i) lo.databodyrange.autofilter lfilter, replace$(fltr.criteria1, aoper(i), aopp(i)) exit end if next end sub your example happened inverting number, if want universal (apply nonnumerics), lot more complicated.
update
this invert value lists, makes assumptions. one, if have 2 values, it's not value list, it's xlor operator. if you're using xlor on other type of field, might cause problems.
sub invertfilter() dim lfilter long dim lo listobject dim fltr filter dim aoper variant, aopp variant dim long, j long dim dc scripting.dictionary dim vavalues variant 'find column you're in set lo = activecell.listobject lfilter = activecell.column - lo.databodyrange.column + 1 set fltr = lo.autofilter.filters(lfilter) 'lists of values or 2 values if fltr.operator = xlfiltervalues or fltr.operator = xlor 'get possible values , put in dictionary vavalues = lo.listcolumns(lfilter).databodyrange.value set dc = new scripting.dictionary = lbound(vavalues, 1) ubound(vavalues, 1) if not dc.exists("=" & vavalues(i, 1)) dc.add "=" & vavalues(i, 1), "=" & vavalues(i, 1) end if next 'if it's more 2 values if isarray(fltr.criteria1) 'remove dictionary = lbound(fltr.criteria1) ubound(fltr.criteria1) if dc.exists(fltr.criteria1(i)) dc.remove fltr.criteria1(i) end if next else dc.remove fltr.criteria1 dc.remove fltr.criteria2 end if 'reapply filter lo.databodyrange.autofilter lfilter, dc.keys, xlfiltervalues elseif fltr.operator = 0 'aopp opposite of corresponding 'operator in aoper aoper = split("<> <= >= = < >") aopp = split("= > < <> >= <=") 'if first characters of criteria in aoper 'then swap them aopp = lbound(aoper) ubound(aoper) if left(fltr.criteria1, len(aoper(i))) = aoper(i) lo.databodyrange.autofilter lfilter, replace$(fltr.criteria1, aoper(i), aopp(i)) exit end if next end if end sub 
Comments
Post a Comment