Next cell with data?

  • Re: Next cell with data?


    try this...


  • Re: Next cell with data?


    gijsmo, Thanks for the help. It sort of works in that it will jump to the next row with data in however I only want it to look in the current column, not the other columns. Does that make sense?


    Thanks again

  • Re: Next cell with data?


    Let me try to explain a little better. If my cursor is in cell F5 I would like it to jump down column F to the next cell with data in column F, whether it be F6 or all the way down to F100. It doesn't matter which column it just needs to be the active cell column. Does that make more sense? If someone could point me in a direction I will try to work it out.


    Thanks in advance.

  • Re: Next cell with data?


    Not sure how you are using the code but that's exactly what the code is designed to do.


    The code starts from the current active cell, eg column F row 5 and will then jump down to the next non-empty cell in the column until it gets to the last cell in the column.

  • Re: Next cell with data?


    Quote from gijsmo;764484

    The code starts from the current active cell, eg column F row 5 and will then jump down to the next non-empty cell in the column until it gets to the last cell in the column.


    Yes it does this but if there is nothing in column F until row 20 it stops on row 10 because there is something in A10. I only want it to look in the current cells column.

  • Re: Next cell with data?


    That still seems odd to me. Without seeing the sheet you are trying to run the code on, I have created a sample sheet with the same macro.
    ozgrid.com/forum/core/index.php?attachment/68159/


    Just put your cursor in any column with data and use Ctrl+Shift+J to activate the macro...for example it will jump from F5 to F11 even though though there is data in other rows.


    This tends to suggest to me that the data you have is not truly "empty". You can test this by manually placing the cursor on F5 in your sheet, then press the End key and then press the Down arrow (simulating what the macro does).
    If the cursor moves to F10 instead of F20 as you would expect then F10 is not "empty".

  • Re: Next cell with data?


    Ahhh.....I see said the blind man. My sheet is generated from another program and it is putting in 0's where there "APPEAR" to be blanks. I never realized it did that. My apologies if I ruffled any feathers previously.


    Now that you have sorted that out can it be modified to find the next value greater than or less than 0 and jump to it?


    Thanks in advance.

  • Re: Next cell with data?


    Based on the sample you sent me, the following should do the trick of jumping to next non-zero row in a column relative to the ActiveCell.


  • Re: Next cell with data?


    Quote from gijsmo;764515
    Code
    1. With ws1.UsedRange
    2. 'find a spot to place a temporary formula
    3. lRight = .Columns.Count + 1
    4. 'work out the last row
    5. lEnd = ws1.UsedRange.Rows.Count
    6. 'make sure there is a row left to jump to
    7. [B][COLOR=#FF0000]If lTop > lEnd Then Exit Sub[/COLOR][/B]
    8. End With


    It ends after the section in red. lTop is always 1 more than the current row number and lEnd is always 1.

  • Re: Next cell with data?


    I did some digging around and I found this code that will return the last row used.


    However I would not want it only for sheet 1. Just thought this might help you.

  • Re: Next cell with data?


    The code was written using the testdata file you posted. It works fine on my copy with code attached.
    ozgrid.com/forum/core/index.php?attachment/68169/


    Not sure how you are invoking the macro to test it.
    It is entirely dependent on the current ActiveCell location so, for test purposes, I have assigned the macro to the Ctrl+Shift+J hot key sequence so the cursor is not moved from the ActiveCell inadvertently.

  • Re: Next cell with data?


    WOOHOO! I finally got it to work. I do have one small request though (I think it's small anyway).


    I store most of my code in a "Personal" file so any open file can have access to it. This portion of your code "Set ws1 = ThisWorkbook.ActiveSheet" references the file where the code is stored which is empty. Is there an easy way to change "ThisWorkbook" to maybe the active workbook or something?


    Other than that it works like a charm!


    Thank You! Thank You! Thank You!
    Will

  • Re: Next cell with data?


    Activeworkbook would fix the issue if you're storing it in your personal workbook (or simply Set ws1 = Activesheet).


    The formula that determines where to jump to next is designed as per specifications to jump to next non-zero row in the column. It assumed you would start at row 1.
    In your random sample of data, your first row is 5 not 1 so the calculation for the end row is not correct.


    Change the code from :
    lEnd = ws1.UsedRange.Rows.Count
    To
    lEnd = .Rows.Count + .Row


    and it should work for all data even if you don't start at row 1.


    NOTE: jumping to next "non-zero" row works on most data including text but may not jump as you expect if there is a "null" in the cell - these appear to be empty but aren't.