Correct syntax for .find

[insomniac53]

I tried to avoid asking this as similar questions have been asked many times, but I can't see the solution I need.

Briefly, I need a function [NextNonBlankRow], given a specific row number as a parameter, to return the next non-blank row, searching down.

So, if A100 contains a value and A105 contains a value, whilst all other cells are empty, then my function would be sent 100 and return 105.It would always search the same column so there's no need to send it the column letter - let's use A as the column for simplicity.

I am working along these lines but the syntax is wrong:

function NextNonBlankRow(rowstart as long) as long
    NextNonBlankRow = Find(what:="*", after:=w1.Range("A" & rowstart), LookIn:=xlValues, SearchDirection:=xlDown).row
end function

Thanks!

 

[Fluff]

How about

Function NextNonBlankRow(rowstart As Long) As Long
    NextNonBlankRow = Range("A:A").Find(what:="*", after:=Range("A" & rowstart), lookIn:=xlValues, SearchDirection:=xlDown).Row
End Function

 

[JoeMo]

Another way. This returns the next non-blank row in the column that the first cell of range R is in.

Function NextNonBlankRow(R As Range) As Long
Set R = R(1)
NextNonBlankRow = R.End(xlDown).Row
End Function

 

[insomniac53]

I will need to try these - thank you for the replies - but the column in question will have multiple cells that hold values, so I can't set the range as R(1) if that starts searching from the first row (JoeMo).

Would this work (Fluff)

NextNonBlankRow = Range("A" & startrow & ":" & "A").Find(what:="*", after:=Range("A" & rowstart), lookIn:=xlValues, SearchDirection:=xlDown).Row

 

[insomniac53]

Another way. This returns the next non-blank row in the column that the first cell of range R is in.

Function NextNonBlankRow(R As Range) As Long
Set R = R(1)
NextNonBlankRow = R.End(xlDown).Row
End Function

I've adapted this so that the range is set to the start cell and it works. I could either pass the range as a parameter, as you've done, or stick to my original and calculate the range within the function - but either way, the syntax works and that's what I needed. Peace at last - till next time. Thank you.

 

[JoeMo]

You don't need to adapt what I posted. R(1) is the first cell of the range you pass to the function or the range you select with a mouse if you are using this as a worksheet function. For example if R is say A9:Z9, R(1) is the range (a single cell) A9 (first cell in the range R).

In any case, glad this helped you.

 

[Rick Rothstein]

You don't need to adapt what I posted. R(1) is the first cell of the range you pass to the function or the range you select with a mouse if you are using this as a worksheet function. For example if R is say A9:Z9, R(1) is the range (a single cell) A9 (first cell in the range R).

Some possible problems with your code (not sure if the OP would ever run into them though)...

1) If the specified range starts on the last cell with a value in the column (all cells under it are blank), then your code returns the last row number on the worksheet (1048576 in my version of Excel).

2) If the first cell of the specified range has a value and all the cells under it within the range are blank and there is a cell with a value below the specified range, the row number for that cell outside of the specified range is returned.

By the way, I have no idea what the OP might want the function to return should either of the above scenarios occur.

 

[insomniac53]

Some possible problems with your code (not sure if the OP would ever run into them though)...

1) If the specified range starts on the last cell with a value in the column (all cells under it are blank), then your code returns the last row number on the worksheet (1048576 in my version of Excel).

OP here - no, I shouldn't run into them, but if I mess up, then I'll check for the final row number and treat that as an error.

2) If the first cell of the specified range has a value and all the cells under it within the range are blank and there is a cell with a value below the specified range, the row number for that cell outside of the specified range is returned.

OP here again - unless I've misunderstood, there is no specified range, just a specifed row as a parameter to the function, so if all the cells below are blank, then this is the same scenario as 1.

By the way, I have no idea what the OP might want the function to return should either of the above scenarios occur.

OP: neither do I. It shouldn't happen, but if the absolute final row number is returned, as above, that will be classed as an error and dealt with.

I'm very happy with the way the code works. Thanks for all replies.

 

[JoeMo]

Some possible problems with your code (not sure if the OP would ever run into them though)...

1) If the specified range starts on the last cell with a value in the column (all cells under it are blank), then your code returns the last row number on the worksheet (1048576 in my version of Excel).

2) If the first cell of the specified range has a value and all the cells under it within the range are blank and there is a cell with a value below the specified range, the row number for that cell outside of the specified range is returned.

By the way, I have no idea what the OP might want the function to return should either of the above scenarios occur.

I assumed Scenario 1 is unlikely. Scenario 2 is exactly what I believe the OP would want to happen.