Excel vba to select next option in autofilter drop down menu -

i have several column few hundred rows of data. 1 of roles through data (most commonly in column 2), click little drop down arrow on column header open auto filter list, deselects first value, select next value. then, likewise, open menu, deselect second value , select third.

there's no fixed number of values either. different data sheets have varying amounts of data. data goes 0,10,40,50,60,.... again isn't fixed. array however. data in increasing order already.

what need:

1. preferably button click (for column 2) deselects selected value, selects next value , filters out
2. the converse. i.e. deselects current value, selects previous value

essentially need forward , back button data.

this when tried record actions.

sub a()  activesheet.range("$a$2:$v$609").autofilter field:=2, criteria1:     ="750385/000"     activesheet.range("$a$2:$v$609").autofilter field:=2, criteria1:     ="750385/010" activesheet.range("$a$2:$v$609").autofilter field:=2, criteria1:     ="750385/017" 

end sub

appreciate help!!

i use spinbuttons on sheet , link them first cell of column, want filter.

(i called spbfilterchange , linked $b$1)

(picture upload doesnt work here, sorry)

then can put following code in module of worksheet:

private sub spbfilterchange_spindown()     change_filter me.range(me.spbfilterchange.linkedcell), false end sub  private sub spbfilterchange_spinup()     change_filter me.range(me.spbfilterchange.linkedcell), true end sub 

and following sub in standard module:

option explicit  sub change_filter(sortfield range, boolean) dim filter_values collection dim value_arr, val, sort_value string application.screenupdating = false     ' find unique values in relevant column -> collection     set filter_values = new collection     sortfield.offset(2, 0).areas(1).autofilter sortfield.column     value_arr = sortfield.parent.range(sortfield.offset(3, 0), sortfield.parent.cells(sortfield.parent.rows.count, sortfield.column).end(xlup)).value2     on error resume next     each val in value_arr         filter_values.add val, cstr(val)     next val      ' check if value of linkedcell in range     if sortfield.value < 1 or sortfield.value > filter_values.count sortfield.value = 1      ' set autofilter     sort_value = filter_values(sortfield.value)     sortfield.offset(2, 0).autofilter sortfield.column, sort_value application.screenupdating = true end sub 

this should solve problem , used on different columns , sheets (you have add copy of event-procedures in worksheet-module).