Count number of rows in worksheet

Question

I want to count number of rows in Sheet1, from the Sheet2 code module.

In the sheet1 code module, the following code works fine

ctr = Range("B2", Range("B2").End(xlDown)).Count

I tried the same code in the Sheet2 code module

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

I am getting run time error 1004 Application -Defined or Defined error

Thanks

Answer 1

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