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

[robzrob]

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]

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]

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 andit 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).  

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.