Wrong result returned by UsedRange.Rows.Count
 

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,
--
Jeff

Wrong result returned by UsedRange.Rows.Count
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"j" wrote in message ...
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,
--
Jeff

Wrong result returned by UsedRange.Rows.Count
 

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

--
HTH

Bob Phillips

"j" wrote in message ...
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,
--
Jeff

Wrong result returned by UsedRange.Rows.Count
 

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"j" wrote in message ...
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,
--
Jeff