Count number of rows in worksheet

Accepted answer +7 upvotes

The error occurs in the 2nd range reference in recct. Because you are referencing a different sheet, you need to tell VBA the sheet name in both range references.

Try this instead:

With ThisWorkbook.Sheets("Sheet1")    
    recct = .Range("B2", .Range("B2").End(xlDown)).Rows.Count    
End With

Alternatively, this will work as well (though a bit sloppier).

recct = ThisWorkbook.Sheets("Sheet1").Range("B2", ThisWorkbook.Sheets("Sheet1").Range("B2").End(xlDown)).Rows.Count

Update

Since there is a lot of discussion around what you actually mean by number of rows on the sheet, use the above code to literally start at B2 and count the number of contiguous cells directly underneath

However, if you want to find the last “real” used cell in column B (by real, I mean with data in it) do this:

With ThisWorkbook.Sheets("Sheet1")

    recct = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Rows.Count

End With