CODEWITHSUNDEEP
vbaexcel
Matthias Pospiech
Matthias Pospiech

Reputation: 3494

vba: find last row fails with error 91

I am using this code from http://ccm.net/faq/14494-vba-last-non-empty-row-all-versions 

Function lastRow(sheet As Worksheet) As Long

lastRow = sheet.Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row

End Function

It works if the sheet contains something. If it is empty or has had content it fails with error 91 (object variable or with block variable not set).

Why ?

Upvotes: 0

Views: 645

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Function lastRow(sheet As Worksheet) As Variant
   Dim r As Range, lr As Long
   Set r = sheet.Columns(1).Find("*", , , , xlByColumns, xlPrevious)
   If r Is Nothing Then
      lastRow = "Nothing in column"
   Else
      lastRow = r.Row
   End If
End Function

Upvotes: 5

Related Questions