Find next blank row but not past row 35?

[Margate]

Hello Everyone,

I want a macro that will find the last blank row but only up to row 35 (not beyond row 35). I found the below macro online
and it works very well but on my spreadsheet it will go past row 35! The range I want to look in is from cell B11 down to
cell B35. I need to select the first cell within that range that does not contain and data...


Perhaps the below code is able to be modified to not search past row 35?:

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


Thank you in advance for any help.

Margate

 

[Jerry Sullivan]

Hi Margate and Welcome to the Board,

Here's one way...

Sub FindFirstBlankInRange()
    Dim rFound As Range

    
    With Range("B11:B35")
        Set rFound = .Find(What:="", After:=.Cells(.Rows.Count, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
    End With

    
    If rFound Is Nothing Then
        MsgBox "No blanks in this range"
    Else
        MsgBox "first blank is cell: " & rFound.Address
    End If
End Sub

Edit: The above code will find the first blank cell, even if it has a formula that returns a blank.
If you want the first empty cell, modify this line to read...

Set rFound = .Find(What:="", After:=.Cells(.Rows.Count, 1), _
            LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlNext)

 

[Margate]

Wow!
I would never had been able to work that out!
Thank you very much for your help. It worked perfectly. You must be an Excel genius!