+ Reply to Thread
Results 1 to 4 of 4

Wrong result returned by UsedRange.Rows.Count

  1. #1
    j
    Guest

    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. #2
    Ron de Bruin
    Guest

    Re: 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" <[email protected]> wrote in message news:[email protected]...
    >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. #3
    Bob Phillips
    Guest

    Re: Wrong result returned by UsedRange.Rows.Count

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

    --
    HTH

    Bob Phillips

    "j" <[email protected]> wrote in message news:[email protected]...
    > 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. #4
    Ron de Bruin
    Guest

    Re: 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" <[email protected]> wrote in message news:[email protected]...
    > 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" <[email protected]> wrote in message news:[email protected]...
    >>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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1