Count the number of rows in another sheet

8
votes

I have looked at the suggested questions to find the answer to my problem. The closest question is called: Count number of rows in a different Excel Sheet Count number of rows in a different Excel Sheet

The solution to that 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 it is the second worksheet in the workbook. I have tried:

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

None of them seem to work.

2
vbaexcelwith-statement

2 Answers

14
votes

Your original was not working because the parent of Cells(4, 1) and Cells(4, 1).End(xlDown) was not specified. Prefix any cell address with a period (aka . or full stop) when you are inside a With ... End With block. Example:

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

Note the .Cells(4, 1) and not Cells(4, 1). The period specifies that the cell(s) you are referring to are within Worksheets("Verbs").

4
votes

Check this and hope this will help 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 row count.

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: There are lots of answers to your questions. Check this SO link: How can I find last row that contains data in the Excel sheet with a macro?