Count the number of rows on another sheet

Question

I reviewed the proposed questions to find the answer to my problem. The closest question is called: Number of rows in another Excel sheet. Count the number of rows in another Excel sheet.

Solving this problem does not work for me.

I am trying to count the number of rows in a range in a different worksheet than the active worksheet. Here is my code:

Sub verbflashcards() Dim wordcount As Long With Worksheets("Verbs") wordcount = .Range(Cells(4, 1), Cells(4, 1).End(xlDown)).Rows.Count End With MsgBox (wordcount) End Sub 

I have a worksheet called "Verbs" and this is the second worksheet in the book. I tried:

 With Verbs With Sheet2 With Sheets("Verbs") With Sheets("Sheet2") 

None of them seem to work.

Answer 1

Check it out and hope this helps you:

 Sub verbflashcards() Dim wordcount As Long wordcount = ActiveWorkbook.Worksheets("Verbs").Range("A4", Worksheets("Verbs").Range("A4").End(xlDown)).Rows.Count MsgBox (wordcount) End Sub 

Where D1 is the column from which you can get the number of rows.

Method 2:

 Sub verbflashcards() Dim wordcount As Long With Sheets("Verbs") wordcount = .Range("A" & .Rows.Count).End(xlUp).Row End With MsgBox (wordcount) End Sub 

Note. You will find many answers to your questions. Check out this SO link: How to find the last row containing data on an Excel worksheet with a macro?

Answer 2

Your original did not work because the parent of Cells(4, 1) and Cells(4, 1).End(xlDown) not specified. The prefix of any cell address with a period (aka or a complete stop) when you are inside the With ... End With block. Example:

 With Worksheets("Verbs") wordcount = .Range(.Cells(4, 1), .Cells(4, 1).End(xlDown)).Rows.Count End With 

Pay attention to .Cells(4, 1) , not Cells(4, 1) . The period indicates that the cell (s) you are referring to is within the Worksheets ("Verbs").