Get second last row?

[TheWennerWoman]

I have some data in column B. It starts at row 14 and the last possible row of data is row 63.

However, there is text in cell B66 (along the lines of "by submitting this form you confirm you have read the expenses terms and conditions").

So column B might have data all the way down to B63, it might only go to B20, it's fluid.

How can I ignore B66 and just get the second last row?

Many thanks as always.

 

[Alex Blakenburg]

If you are happy to hard code B66 then something like this will work.
Note: Consider at least giving B66 a range name and using that so if you insert rows it will still work.

    Dim lastRow As Long
    lastRow = Range("B66").End(xlUp).Row   ' preferably add a sheet reference before the "Range"

 

[bobsan42]

something like this:

Function slr(sh As Worksheet)
    With sh.Range("B66")
        If IsEmpty(.Offset(-1).Value) Then
            slr = .End(xlUp).Row
        Else
            slr = 65
        End If
    End With
End Function

call it like this in the immediate window of VBE:

?slr (thisworkbook.worksheets("Sheet1"))

 

[bobsan42]

on a second thought, if you are certain that B63 is the last possible entry, maybe this approach is better:

Function slr(sh As Worksheet)
    With sh.Range("B63")
        If IsEmpty(.Value) Then
            slr = .End(xlUp).Row
        Else
            slr = .Row
        End If
    End With
End Function

 

[TheWennerWoman]

Thanks both, either works just fine so I'm happy.