macro to select next in data validation list

[ryan00davis]

ok, im going to apoligize in advance for the length of this post, but i rather give too much info than not enough.

i should start off by saying that i would consider myself rather advanced with formula editing, but very inexperienced with macros.

i have a worksheet that has a data validation list in it, and i want to write a macro that will select the next new value in that list. for example, if i have a list like this (and yes, it will be in alphabetical order):

apple
banana
banana
grape
orange
orange
orange
peach

and the current selection is banana, i want it to automatically find the next new value, so even if it has the first banana selected, i want it to move to grape.

the overall goal of this macro will be to:
select the next in the list,
the rest of the spreadsheet will automatically update based on the formulas,
then it will hide certain rows of the worksheet based on given values,
print the sheet,
unhide all rows,
select next value and repeat until it reaches the end of the list.


the print statement will look something like this:

Please Login or Register  to view this content.

unhide will be:

Please Login or Register  to view this content.

so essentially, what i need help with is the selecting next in the list, then i will need to be able to hide a row based on whether or not the sum of columns e through i are greater than $10 in a given row, but im pretty sure i could figure out the hide statement.

thanks in advance for the help, and you guys have helped me more than you know just by searching through the forums.
-Ryan

[rylo]

Ryan

I'm guessing that you want to build some sort of loop that will cycle through the unique items in the data validation list, post that value somewhere, do some actions (hide rows, print...) and then finish.

If so here are some snippits that may help.

1) Say your list as show above is in the range A5:A12. The code below will build a list of unique items.

Please Login or Register  to view this content.

2) Say you want to put each of these items in sequence into cell E2

Please Login or Register  to view this content.

3) Your other code would go into the commented range to action for that particular value of the list.

HTH

rylo