Wrong result returned by UsedRange.Rows.Count

[j]

I have been told that a colleague is having problems with
UsedRange.Rows.Count returning a very large value that far exceeds the
actual number of rows in the worksheet. Since we are iterating through
the rows, although there are work-arounds we can use to identify the end
of the data, it is a nuisance that this property seems to be unreliable.


Has anyone else had a similar problem? Does anyone know of a cause? Is
there a more reliable way of identifying the number of used rows in a
worksheet, in order to iterate through them?

I think that the code is currently running on Windows XP against Excel
2003, although it was originally developed and tested on Windows 2000
against Excel 2000. The object variables are all late bound.

Regards,

 

[Ron de Bruin]

Hi j

Yes this is a problem
I always use a function to know the row number with the last data

Sub test()
MsgBox LastRow(ActiveSheet)
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


You can also check one column
MsgBox Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

 

[Bob Phillips]

See http://www.contextures.com/xlfaqApp.html#Unused

 

[Ron de Bruin]

More info here
http://www.contextures.com/xlfaqApp.html#Unused