Solved: Counting to Last Cell

[Opv]

Why is this code resulting in a count of 1 for a sheet that has nearly 400 rows of data? The count for rows reports the proper number. However, the myRows count returns 1. I've even changed the range to Range("A4:A5000") and it still returns 1.



Sub countEntries()

'Dim myRows As Integer
Dim myCols As Integer

myRows = Range("$A4").End(xlDown).Count
myCols = Range("$A4:C4").Count

MsgBox myRows
MsgBox myCols

End Sub

[Paul_Hossler]

.End () goes from the starting cell to the cell before the first blank one, or to the bottom of the sheet. Does not necessarily take you to the end of your data if there are gaps.

If you select A4 and then press End and the DownArrow, where does it take you?

What you're doing is going to and counting the .End row, and since the end row is only a single row, it Count's = 1

Try Range ("A4", range("A4").End(xlDown)).rows.count (not tested)

Otherwise post a WB

Paul

[Opv]

.End () goes from the starting cell to the cell before the first blank one, or to the bottom of the sheet. Does not necessarily take you to the end of your data if there are gaps.

If you select A4 and then press End and the DownArrow, where does it take you?

What you're doing is going to and counting the .End row, and since the end row is only a single row, it Count's = 1

TryRa nge ("A4", range("A4").End(xlDown)).rows.count (not tested)

Otherwise post a WB

Paul

If I select A4 and do the END and DOWN ARROW, it takes me to the last row that contains data.

The suggested change works.

Thanks!

[austenr]

try
Sub countEntries()

'Dim myRows As Integer
Dim myCols As Integer

myRows = Range("$A4:A5000").Count
myCols = Range("$A4:C4").Count

MsgBox myRows
MsgBox myCols

End Sub

[Opv]

try
Sub countEntries()

'Dim myRows As Integer
Dim myCols As Integer

myRows = Range("$A4:A5000").Count
myCols = Range("$A4:C4").Count

MsgBox myRows
MsgBox myCols

End Sub

That reports a count of 4,997. The first suggestion reported the correct number of rows.

Thanks,

Opv