Keep jumping consecutively to next blank cell in non-contiguous vertical range

Hi Stewart,

I think this is what you're after:

Please Login or Register  to view this content.

Regards,

Robert

hi Robert

i applied your code, it works by going only to the first blank, but that's where it stops right there, despite further clicks of the button Activecell won't jump to the next blank. Maybe somewhere needs tweaking ? or, you probably didn't fully grasp what i needed, there are a no.of blanks within the non-contiguous AW9:AW, and i need to keep going to each blank cell by that i mean, each click of the button needs to send the Activecell to the next blank further down. I need to do this regularly to analyse the adjacent data on each row

Stewart

Try this:

Please Login or Register  to view this content.

If I understand what you want correctly, then I think this macro should do it for you...

Please Login or Register  to view this content.

Note: I omitted your first two lines of code that recalculate the worksheets... unless you are working in Manual Mode, I do not see a need for them and surely then you do not need to execute them every time you execute this code.

@Robert & @Rick

Both your codes are absolutely fabulous as both worked, & just out of need, i compared their execution speeds - seemed both initiated and were running the same task with the same speed.

The only difference is Robert's latest code knew to stop and prompt the msgbox when no more blanks are found below, so i used Robert's code.


cheers
Stewart

Hi Stewart,

I'm glad we were able to provide you with a suitable solution and thank you for the rep.

Regards,

Robert

Originally Posted by MannStewart

The only difference is Robert's latest code knew to stop and prompt the msgbox when no more blanks are found

Argh! Bad testing on my part. For the record, here is a modification to my code to handle this oversight on my part...

Please Login or Register  to view this content.

Interestingly, my new code is one code line shorter than my original submission.

NOTE: Regarding this part of what I quoted above... "and prompt the msgbox when no more blanks are found". Your original code used Column AV to determine where the last row was located. Just pointing out that my code used that also so that if there are blanks after the last value in Column AW that occur before the last row in Column AV, my code will visit those blanks... Robert appeared to take your statement at face value and does not look for blanks after the last value in Column AW. Based on your original code and what you wrote in your last message, I am not entirely sure which of our implementations actually meets your real requirement... something for you to check.

hi Rick

no, both AV & AW don't work as lastrow because AV is formulated all the way down past lastrow and AW is non-contiguous (at the time i created this workbook i didn't know about Excel's TABLE feature, so i went the long way and now i have a very large worksheet which i have to copy & paste formulae every time i have to expand my work, many columns are functions formulae down to 10,000 in preparation for future use).

I used col D which is a contiguous & mandatory (by way of custom formula in Data Validation) INPUT range as the lastRow for your code to work., is this what you were referring to..?

hi Robert

i just tried locking the sheet before i was about to shut down my PC, and i discovered your code doesnt work after Protecting, every click just comes back with the preset msgbox " No more UnRECON OPEN" as if it can't see the blanks anymore.
Which column in the code is the Protection affecting ?

AV is locked as it is formulated. AW9 downwards is not locked as it is INPUT range, so what is causing this after Protecting the sheet?

Hi Stewart,

The sheet has to be unprotected or else the rngBlanks range variable doesn't get set and hence the "No more UnRECON OPEN" message appears.

The code needs to unprotect the sheet while being executed so try this:

Please Login or Register  to view this content.

HTH

Robert

Originally Posted by MannStewart

no, both AV & AW don't work as lastrow because AV is formulated all the way down past lastrow

Let me ask a different way. Is it possible for their to be blanks past the last displayed value in Column AW but before the last displayed value in Column AV that you would want the code to select?

EDIT NOTE: I just noticed you marked this thread SOLVED so there is no need to answer the above question as you apparently have a code solution you are happy with.

hi Rick

no it is not possible for there to be blanks in AW past the last displayed value in Column AV, last displayed value in AV is where column D is last filled, next row in D, AV & AW after that are all blank (only AV has formula), and that is the last row for staff users to look at. Column D determines lastrow because it is a date INPUT. Where D is filled, AV must show a value. But before the last displayed value in Column AV, AW is full of blanks anywhere. That's why i said AW is non-contiguous. Inside AW as soon as starts from AW9, are dotted with blanks in random places, because I wouldn't know which row the staff teachers would leave blank AW. Users must enter D, then AV calculates a value, and the user decide if they want to enter AW or not on that day. If they don't they can do it later on another day. AV is a self-calculating formula from row 9 down to 10,000 which does NOT depend on AW, so AV returns a value no matter AW on same row is blank or filled. I couldn't use AV as lastrow because the code treats the formula functions down to 10,000 as non-blanks.

By the way, your code delivers the same result I need. However, compared to Robert's code, after several more comparison tests i noticed that perhaps because of your code's start section where you based it on my old code by start jumping off from AW5, so it kind of lags about 1s. I'm currently trying to figure out an alternative way for the start section to bypass this to see if it be sped up. I don't know if the old code got slower because of that AW5 factor too.

hi Robert

Fantastic ending. It works under Protected mode now.


cheers
Stewart

Ideally the activecell (which are only those naturally unlocked ones) no matter where the user is at

is there any way for the code to go straight to the first blank cell in AW9:AW no matter where activecell currently is at? By now i have already realised it was my mistake a year ago to use AW5 as a starting base to start off which is totally redundant and unnecessary.

hi Rick

you almost understood me, except for the row part, sorry if my lengthy description was the hurdle because of the nature of the user's daily work.

Nonetheless with your 2nd code, i amended to as follows and it works now without the 1s lag:

Please Login or Register  to view this content.

The AW5 must have been the issue that was causing the lag.


have a good weekend,
Stewart

hi guys,

i wasn't sure to start a new thread or expand on this one as it is a(nother) related question and as I'm using both your codes for various workbooks, i thought i should continue with this thread. I studied both codes but I didn't have the capability to adapt your codes to fit the following new criteria:

So, I have another button that I need to do similarly, to jump to the next blank, similarly 1st data row starting from row 9, data this time is in column BP9:BP (i believe it's column 68). The date column D still determines lastrow. This time, the find criteria is to jump to the NEXT BLANK in BP9:BP which ALSO is accompanied with a FILLED NUMERIC VALUE in its adjacent column BO (on same row with BP). For example, some BP9:BP may be blank but its adjacent BO could be filled with a TEXT value, or BO could be just empty, then these types of blank BP9:BP should just be skipped and not to be bothered with by the macro. BO9:BO is auto-calculated by formula, its value may be TEXT or NUMERIC, filled or blank.

So, the only criteria is above, go for the next BP blank that has an adjacent BO that is filled & NUMERIC.

Kindly let me know how the modification should be done



cheers
Stewart