Search code examples
vbaexcelwith-statement

Count the number of rows in another sheet

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.

Solution

  • 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?