Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wrong output returned during IF function | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
Real Value of .UsedRange.Rows.Count | Excel Worksheet Functions | |||
Problem with UsedRange.Rows.Count | Excel Programming | |||
Finding row count and filtered rows returned by Autofilter | Excel Programming |