2 buttons Macros just to get to subsequent blank cell, ea with slightly different criteria

Hi again MannStewart,

You mentioned in the previous thread that my code was your best option, but I see that the codes in this book are quite different. Was your intention to essentially use my approach, but with added criteria?

Additionally, why start at row 9? Doesn't data start on row 3? And... why are you checking offset(1,-30)> that looks at column S, but 1 row below the current row?

Hi Arkadi!

yes, I actually only tried your code after posting in this thread, because I carelessly thought Karmapala's code worked, after trying his code first (false hope & my mistake). After countless trials and failures losing sleep adjusting your code I arrived to the best of my limitations to like this (below), 1 for each section, but the issue i have now is they are still slow in my main workbook which goes right now all the way to row 6,000+. I realised in my trials that your code is actually looping by activating and 'resting' before continuing to next offset from each new blank that meets the criteria, thus making the loop somewhat slowing the worksheet on each click, and although your suggested was use ScreenUpdate=False, the page does not follow down to the ActiveCell that are much lower down, so I used SmallScroll but it makes the sheet rapidly flash & it gets nauseating after awhile on every click. Do you have any way to improve the search speed & how to pan the screen so that activecell is always 2 columns from the RIGHT side of the screen (not left) without SmallScroll or Application.Goto ActiveCell, Scroll:=True ? For section-1, have to see AK & AV, and for section-1, have to see BG & & BP, note that there are a number of used columns in between so the column AV and BP must be pushed to the extreme right side of the screen that's the place I can view AK for sec-1, and BG for sec-2 on each click.


There are title headers from row 1 to 8, it's due to the need to describe each column by notes which are necessary, so all data start from row 9.
For sec-1, I use column S (-30) , or as a 'reliable' ALWAYS-FILLED column for the loop to stop because your code kept going all the way down past rows 6,000+ i didn' t know how else to stop the process once no more data is reached so I thought I should rely on S which is always filled (-11 is always filled like -30, but nearer & does the same for sec-1, -18 is always filled till last filled row for sec-2).

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Looking forward to your reply,

Stewart

Your sample books really should reflect the layout of the real thing... makes it easier to understand. I am referring to rows in sample starting at 3, while you explain it is 9.

In any case... If I've understood what you need, then try this for the AW column.... I'll work on the other one after we find out if I've understood. Also want to know if this is truly faster or not.

Please Login or Register  to view this content.

I may re-write this later to work with an array instead of ranges if the speed is still not good.

hi Arkadi

yes, sorry about my sample workbook slip-up on row 5 which is actually row 9.

Thanks for your sec1 code. Yes this one works faster than your original, but it still blinks once on every click & one can kind of catch a glimpse of the lagged cursor jumping, (whereas Karmapala's code does not blink / lag at all & it is still fastest it just skips to the next blank and you can't catch any glimpse of it, although that code hasn't fully worked yet. Is it your suggested method of looping that is resulting in a slight lag & blink on ea click ?) Also, your this code still doesn't pan the screen to the consecutive further blanks active cells below, that i need it to as i mentioned previously.

Any way to?


Stewart

Sorry thought it would pan to the cell.... I’ll revise when I can.

Thanks for the feedback I've been away a few days, I guess you have it under control for now. If you need more help then don't hesitate to ask!

Best of luck!