Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004

    Unanswered: UsedRange problem


    I have been using the extension 'UsedRange.Rows.Count' to find the last row used on a worksheet for longer than I care to remember and have today found a problem.

    I generally produce worksheets that start at Row1, but today had reason to start at Row10.
    The code I have always used to loop from the first to the last line for a worksheet set to ShtName is

    For Looper = 1 to ShtName.UsedRange.Rows.Count

    Imagine my surprise when this stopped 10 lines short of the end. Has anyone else come across this problem and how have they solved it? Is there a way of getting the number of the final row used in a worksheet? I have got around this by inserting a value in cell A1 at the start of the module and then removing it at the end of the module, but this seems to be cheating.

    Any help would be appreciated.

  2. #2
    Join Date
    Dec 2004

    Maybe the answer

    Just found 'UsedRange.End(xlDown).Row'

    Is this the answer or will this give me problems in the future?

    Also found

    ShtName.UsedRange.Rows.Count + ShtName.UsedRange.Row-1

    Ths looks to be a better answer
    Last edited by michaeldavid; 11-18-08 at 07:58.

  3. #3
    Join Date
    Dec 2003
    Ottawa, Canada

    ActiveSheet.UsedRange.Range("A1").Row + ActiveSheet.UsedRange.Rows.Count - 1

    But I prefer using CurrentRegion when I work with database extracts
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick

Posting Permissions

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