Getting to the next (another) balnk cell by vutton click

Maybe try something like this :

Please Login or Register  to view this content.

You need to have the active cell in the regarded column, wherever row.

hi Karmapala & Dangelor,

thanks for your codes, I want to try them but I see you don't specify the start cell, so where in your code do i specify the start cell AW5 and column ? Otherwise, how does Excel know where to start looking ? my file has many other columns although i dont need search there . I need the macro to start at the active cell from AW5 every time I start the workbook, can you show me how your code should be modified?

Stewart

Originally Posted by MannStewart

hi Karmapala & Dangelor,

thanks for your codes, I want to try them but I see you don't specify the start cell, so where in your code do i specify the start cell AW5 and column ? Otherwise, how does Excel know where to start looking ? my file has many other columns although i dont need search there .

Stewart

Hi MannStewart, that's why I put a note :

You need to have the active cell in the regarded column, wherever row.

btw, what do you mean with this ?

the start cell AW5 and column

I thought cell AW5 ---> the column is AW and the row number is 5 ?

Anyway, assuming that cell AW5 is the column is AW and the row number is 5, then the code :

Please Login or Register  to view this content.

Originally Posted by MannStewart

hi,

I need the cell to start its search journey from cell AW5, because sometimes AW6 may be empty, or it may be filled, but AW5 is definitely empty and not meant for any data

I hope u see what I meant ?

Then try to add this line:

Please Login or Register  to view this content.

right below the "Sub test()" line.

Please see my edited code on #6

I need the cell to start its search journey from cell AW5

You can't have the code to have the starting cell selected without a condition.
Because if there is no condition, then each time you run the macro - the code keep "coming back" to cell AW5.
So the added line above is the condition IF the active cell is already in column AW, then do nothing.
IF the active cell is not in column AW then start cell is cell AW5.

MannStewart, please read the forum rules, in particular #2 about using code tags.

Originally Posted by MannStewart

hi Karmapala,

when i do this, your code goes to the 1st blank, but on another click it does not go to the 2nd blank or another blank anymore, it stays on 1st blank and does not move.. Why..?

Please Login or Register  to view this content.

As I mentioned before in my post, the code line above is NO NO .
With the code line like above, each time you start the macro, it will coming back to cell AW5,
which then we can't expect that it will go to the next blank cell from the last active cell.

Please read my post #6 and #8, and try this :

Please Login or Register  to view this content.

And please don't alter the code back to

Please Login or Register  to view this content.

This code assumes that you focus to do only "searching" where is the blank cell in column AW.
So, you keep on hitting the button, without click any cell here and there, especially click cell outside column AW.
If you do not focus, which means after you click the button - then you do something else by clicking another cell outside column AW,
then the code will coming back to cell AW5.

If you do not focus, which menans after you click the button - then you click another cell within column AW
then the code will start from the cell you just click.

The code is not remembering "where is the last active cell" in column AW if you wondering around click another cell here and there after run the macro.

You may want to better explain... the code provided looks for the first blank below AW5. Is your expectation that it goes to the blank cell BELOW the "current" cell? If you are in column AW when this runs, it will select the NEXT blank below. if you are not in AW then it should stop at the first blank below AW5.

Please Login or Register  to view this content.

If you highlight code you type in, then clicking the # button will put code tags around your code... or lead it with [CODE] and then at the end [/CODE]

Thanks for the feedback, and marking the thread as solved.

hi Karmapala

there's just 1 more column that I need to do the same thing, but just a little bit complicated to me. I tried to modify & apply the same code you offered for selecting next blank in this column as follows, but failing miserably, the cursor dives straight down to bottom abyss of the page and ERROR msg pops up. The target column is BP, like previous problem it also needs to start from BP5, but i need to sel the next blank down the column, AND meet the criteria that the column BO to its LEFT is a numerical value & not a text string. The column BO:BO contains data that can be positive numbers AND names of people, and has intermittent blanks as well like BP.

I need to click the new button to sel the next blank which corresponds to a blank in BP, and only if the cell to the left in BO is not blank and is a numerical value (not text) only.

Please Login or Register  to view this content.

Also, how can I add an ending command(s) to both codes to cease going further the searching if the bottom row has been reached, such as a msgbox & go back to the top. What I mean is, further blanks are just complete blank rows below the bottom of the worksheet body, that is the last filled row of column A:A. The body grows and according to A:A which is always filled regardless if AW:AW and BP:BP are blank or not. I think the last command should be to use column A:A to determine when to cease further clicks, but i do not know how to add the instruction to the codes.


Awaiting Help

Stewart

Hi MannStewart,

Please try this

Please Login or Register  to view this content.

Also, how can I add an ending command(s) to both codes to cease going further the searching if the bottom row has been reached

The message box will appear at the time the row in column BP = or more than the last row with value in column BO.

hi Karmapala

this code is perfect for section-2. Except, can you suggest how to add some command so that the column-68 is always 1 column from the RIGHT side of the screen? So that the screen can always display column AV also with BP?

i'll try to modify this code for column-49 too Your previous did not work as it stopped after 2 blanks for some unknown reason.


Stewart

Originally Posted by MannStewart

hi Karmapala
the screen always goes to & displays column AV & BP?

Sorry, I don't really understand what you mean.

Did you mean that when running the macro I put in post #23,
when the code hit the expected result, it display you to that cell in column BP?
If that's you mean, it does bring/display you that cell although the row is exceed the window :
2020-06-06_14-36-53.gif

My actual data goes very deep down to rows approaching 10,000
and the screen needs to show the active cell

As you seen in the image above,
the screen does show the expected cell result.

2. can you modify the previous code for column-49?
That did not work as it stopped after 2 blanks for some unknown reason

2020-06-06_14-50-52.gif

I'm sorry I don't quite understand "it stopped after 2 blanks",
because as seen in the image above, the macro for column-49 doesn't stop after 2 blanks ?

the screen always goes to & displays column AV & BP

When running the sub for column 49 or when running the sub for column 68 ?

I think it would be better if you can attach a sample workbook.
Doesn't have to be all what's in the sheet.
Maybe you can copy just column AW, BO and BP for about 300 rows ?

hi Karmapala

i came up with an alternative (compromised) way, ie. use col-63 instead of 68 as the x-column, in this column i can use BK7 instead of BP5 for starting cell. With this alternative column, I can still view from column BD to BP on each click screen result. But, my code will not search to include the last bottom blank, although there is 1 more last blank below. Can you give me a pointer & help me check see what's wrong with my code?

Please Login or Register  to view this content.

Stewart

Originally Posted by MannStewart

hi Karmapala

i came up with an alternative (compromised) way, ie. use col-63 instead of 68 as the x-column,
in this column i can use BK7 instead of BP5 for starting cell.
With this alternative column, I can still view from column BD to BP on each click screen result.
But, my code will not search to include the last bottom blank, although there is 1 more last blank below.
Can you give me a pointer & help me check see what's wrong with my code?

The below line that you used after you modified the code :

Please Login or Register  to view this content.

Then it seems that the "rule" is not like before anymore, but change to like this :
While the code is checking column BK row n ...
If column BP row n is blank and column BO row n value is number
then select this column BK row n

Am I correct ?
If yes I am correct, then the following line

Please Login or Register  to view this content.

it's telling to loop in column BK to each row
until the BK row number = or more than the last row number with value in column BJ

Now, maybe the last row number with value of column BJ is shorter than the last row number with value of column BO ?
That's why :

my code will not search to include the last bottom blank

MannStewart, you are pursuing this in 2 threads which means we are duplicating our efforts, shall I just stop working on it for now?

hi Karmapala

ok, based on your direction, i modified & i think i got it like this:

Please Login or Register  to view this content.

Thanks very much for your Help.
Stewart

Don’t mark the other as solved until everything is truly solved

Originally Posted by MannStewart

hi Karmapala

yes, the last final row of BK and BP is blank, but BO has data,
so, if i'm telling Excel to loop in BK,
how do i modify the instruction to tell it to loop in BO or column-50
which always has data until final bottom row?

Then the comparison needs to the column BO, MannStewart.

i tried this, it didn't work:

Please Login or Register  to view this content.

Please have a look at the bold code, MannStewart.
The "original" code, the value of col is 68 (column BP).
Then "col minus 1" is 67 (column BO).

Later when the col value changed to 64 (column BK)
then "col minus 1" is 63 (column BJ).
So it still won't work the same as the "original" code.

Originally Posted by MannStewart

hi Karmapala

ok, based on your direction, i modified & i think i got it like this:

Please Login or Register  to view this content.

I don't quite understand how the code line above works as expected.
Because the "Not IsEmpty" cell to check from the code above is column BM, since the column of x is BK.

IF the "original" code line is :

Please Login or Register  to view this content.

since later on you modified column x = 63 (column BK)
then I think the other line should becomes :

Please Login or Register  to view this content.

Anyway, please forget about your modified code.

Just now I looked in the internet and found a new code instruction
which it seems if I add it in the beginning of the "original" code,
the result on the screen is maybe that you expect it :

Please Login or Register  to view this content.

The first line of the sub above is telling something like this :
"scroll column AW to the most left of the screen".

I don't know how is your screen,
but in my screen when column AW is at the most left on my screen,
the most right column on my screen is column BP.

Via F8 in VBA editor, please playing around with the value on the first line of the code,
until you find what you see on your screen fits to a state that you expected.