Find Next Row With No Value In It - But Not Next Row With No FormulaIn It
 

I've got this

nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious,
searchorder:=xlByRows).Row + 1
Cells(nextrow, 1).Select

which will find nextrow with nothing in it and select cell in Col A,
but now I've got formulas in the cells, so instead of finding the next
row with nothing in it, I want to find the next row with no value -
even if it's got a formula in it. Can I do this by amending what's in
the what:="*" part of this code?

Find Next Row With No Value In It - But Not Next Row With No FormulaIn It
 

It's always better to include all the parms for .find(). If you don't, then
you're at the mercy of the previous Find--either by the user or by your (or
other's) code.

And one of those "hidden" options is that you're looking through formulas. If
you specify values, then you may see it work ok.

Another problem with your snippet of code is if there isn't anything found. You
won't be able to add 1 to the row number (since the cell wasn't found and it has
no row!).

I'd use something like:

Option Explicit
Sub testme()

Dim NextRow As Long
Dim FoundCell As Range

With ActiveSheet
Set FoundCell = .Cells.Find(what:="*", _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If FoundCell Is Nothing Then
NextRow = 1 'top row
Else
NextRow = FoundCell.Row + 1
End If

.Cells(NextRow, 1).Select

End With

End Sub

And the asterisk represents a wildcard--any set of characters.

Cells(1) is the first cell (A1 in this example).

robzrob wrote:

I've got this

nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious,
searchorder:=xlByRows).Row + 1
Cells(nextrow, 1).Select

which will find nextrow with nothing in it and select cell in Col A,
but now I've got formulas in the cells, so instead of finding the next
row with nothing in it, I want to find the next row with no value -
even if it's got a formula in it. Can I do this by amending what's in
the what:="*" part of this code?

--

Dave Peterson

Find Next Row With No Value In It - But Not Next Row With NoFormula In It
 

On Mar 5, 1:31*pm, Dave Peterson wrote:
It's always better to include all the parms for .find(). *If you don't, then
you're at the mercy of the previous Find--either by the user or by your (or
other's) code.

And one of those "hidden" options is that you're looking through formulas.. *If
you specify values, then you may see it work ok.

Another problem with your snippet of code is if there isn't anything found. *You
won't be able to add 1 to the row number (since the cell wasn't found and it has
no row!).

I'd use something like:

Option Explicit
Sub testme()

* * Dim NextRow As Long
* * Dim FoundCell As Range

* * With ActiveSheet
* * * * Set FoundCell = .Cells.Find(what:="*", _
* * * * * * * * * * * * * * After:=.Cells(1), _
* * * * * * * * * * * * * * LookIn:=xlValues, _
* * * * * * * * * * * * * * LookAt:=xlWhole, _
* * * * * * * * * * * * * * SearchOrder:=xlByRows, _
* * * * * * * * * * * * * * SearchDirection:=xlPrevious, _
* * * * * * * * * * * * * * MatchCase:=False)

* * * * If FoundCell Is Nothing Then
* * * * * * NextRow = 1 'top row
* * * * Else
* * * * * * NextRow = FoundCell.Row + 1
* * * * End If

* * * * .Cells(NextRow, 1).Select

* * End With

End Sub

And the asterisk represents a wildcard--any set of characters. *

Cells(1) is the first cell (A1 in this example). *

robzrob wrote:

I've got this

* *nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious,
searchorder:=xlByRows).Row + 1
* * Cells(nextrow, 1).Select

which will find nextrow with nothing in it and select cell in Col A,
but now I've got formulas in the cells, so instead of finding the next
row with nothing in it, I want to find the next row with no value -
even if it's got a formula in it. *Can I do this by amending what's in
the what:="*" part of this code?

--

Dave Peterson


Thanks - I'll try that in my next workbook - I'm a bit wary of taking
out sections of code and replacing them - don't want the whole thing
to go haywire. But you've shown me how it works, so I know what to
do, ie insert LookIn:=xlValues in my current code. Thanks.