I wrote a brief function for returning the actual last row in a
worksheet regardless of empty, formerly used rows. Neither
"ActiveSheet.UsedRange.Rows.Count" nor "Cells(Rows.Count,
1).End(xlUp).Row" work consistently enough to be 100% reliable.
Here's the function I came up with that seems to work pretty well. I
know it takes a little more processing than the other two methods, but
if you're more concerned about accuracy then this should work for you:

Public Function GetRowCount() As Double
'gets a count of the used rows in a worksheet
'This is a literal count and DOES NOT take
'empty rows into consideration

Dim dX As Double, dY As Double, dZ As Double
Dim dTemp As Double, dResults As Double

dX = ActiveSheet.UsedRange.Rows.Count
dY = ActiveSheet.UsedRange.Columns.Count

For dZ = 1 To dY
dTemp = Cells(Rows.Count, dZ).End(xlUp).Row
If dTemp dResults Then dResults = dTemp
Next
GetRowCount = dResults
End Function

Of course I'm open to better, more efficient ways of doing this :-)

Thanks!

Cory

http://www.beyondtechnology.com/geeks012.shtml


Posted by John Green:

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub

have been around for ages.

--
Regards,
Tom Ogilvy


" wrote:

I wrote a brief function for returning the actual last row in a
worksheet regardless of empty, formerly used rows. Neither
"ActiveSheet.UsedRange.Rows.Count" nor "Cells(Rows.Count,
1).End(xlUp).Row" work consistently enough to be 100% reliable.
Here's the function I came up with that seems to work pretty well. I
know it takes a little more processing than the other two methods, but
if you're more concerned about accuracy then this should work for you:

Public Function GetRowCount() As Double
'gets a count of the used rows in a worksheet
'This is a literal count and DOES NOT take
'empty rows into consideration

Dim dX As Double, dY As Double, dZ As Double
Dim dTemp As Double, dResults As Double

dX = ActiveSheet.UsedRange.Rows.Count
dY = ActiveSheet.UsedRange.Columns.Count

For dZ = 1 To dY
dTemp = Cells(Rows.Count, dZ).End(xlUp).Row
If dTemp dResults Then dResults = dTemp
Next
GetRowCount = dResults
End Function

Of course I'm open to better, more efficient ways of doing this :-)

Thanks!

Cory

The method Cells(Rows.Count,"A").End(xlUp).Row always works when the column
is fully populated. Use shouldn't always ue column A, because it may not be
the column with the largest number of rows. I often use column b,c,or d
instead of A. Some cases it may be necessary to scan more than one column to
determine the last row, but it is not the general case. The right method is
to be smart and pick the column that is full populated to determine last row.

" wrote:

I wrote a brief function for returning the actual last row in a
worksheet regardless of empty, formerly used rows. Neither
"ActiveSheet.UsedRange.Rows.Count" nor "Cells(Rows.Count,
1).End(xlUp).Row" work consistently enough to be 100% reliable.
Here's the function I came up with that seems to work pretty well. I
know it takes a little more processing than the other two methods, but
if you're more concerned about accuracy then this should work for you:

Public Function GetRowCount() As Double
'gets a count of the used rows in a worksheet
'This is a literal count and DOES NOT take
'empty rows into consideration

Dim dX As Double, dY As Double, dZ As Double
Dim dTemp As Double, dResults As Double

dX = ActiveSheet.UsedRange.Rows.Count
dY = ActiveSheet.UsedRange.Columns.Count

For dZ = 1 To dY
dTemp = Cells(Rows.Count, dZ).End(xlUp).Row
If dTemp dResults Then dResults = dTemp
Next
GetRowCount = dResults
End Function

Of course I'm open to better, more efficient ways of doing this :-)

Thanks!

Cory