Results 1 to 2 of 2

Thread: Reset the Used Range

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2006
    Posts
    74

    Reset the Used Range

    My macro runs a loop on the used range.

    However, if the used range is originally 150 rows, but then I delete a lot of the data and make it 50 rows, it will still run through the original usedrange.

    Anyone know how to fix this? It wouldn't be a problem except for the fact that this is a very slow code as it is.

  2. #2
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Reset the Used Range

    gtg
    The used range is not the mose reliable element in excel. You are probably better off by working out the range programatically.
    Here's an example how.
    VB Code:
    1. Dim rngStart As Range
    2. Dim rngEnd As Range
    3. Dim rngDataArea
    4.    
    5.     'Just a demo starting position
    6.     Set rngStart = Worksheets(1).Range("b2")
    7.     Set rngEnd = rngStart.End(xlDown).End(xlToRight)
    8.     Set rngDataArea = Worksheets(1).Range(rngStart, rngEnd)
    9.     MsgBox rngDataArea.Cells.Count
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

Posting Permissions

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



Click Here to Expand Forum to Full Width