Count number of rows in worksheet
excel, vba
Solution
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
Problem
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