apply ByVal change only to visible cells

hi, i have a sheet that is normally viewed rather small to fit more data in the screen. the actual cell data comes from data validation and can be quite long

i have a ByVal to widen a column on selection so that one can see the whole data validation string, then put it back afterwards

Please Login or Register  to view this content.

the columns are based on date ranges and as a range becomes more than 2 weeks old they get hidden. using this however (for obvious reasons) unhides the columns. is there a way to exclude hidden columns?

i've tried

Please Login or Register  to view this content.

and a few other variations. the problem i see is my range will always be from 8 to 50, but the hidden range will change depending on the current date.

thanks for any hints.