Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insight into UsedRange (Excel 2003)

    Hi, all. I was hoping that someone could help enlighten me as to the mysterious ways of the UsedRange property. I'm not putting this in the VBA forum, because my interest in it start outside the realm of VBA.

    Specifically, I'm assuming that, as with many Office terms, the idea of a UsedRange means something else to Microsoft than to the average user. For example, I have many spreadsheets where CTRL+End will usually select a cell several rows (or more) below the last row of actual data and formatting. Normally, I can select the (to my eye) empty rows and delete them, and then when the file is later re-opened, the "last cell" is where I expect it to be.

    While this certainly isn't a show-stopper, it does make for extra work when writing code. I figured it was about time to try and learn exactly what's going on there, especially in light of the fact that a project is in the works that will use hundreds of spreadsheets consisting of thousands of rows each. While a little looping never hurt anybody, a LOT of looping may not be quite as painless.

    Can someone help explain the mysteries of the UsedRange?
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insight into UsedRange (Excel 2003)

    The definition of UsedRange that I use is:

    The range of cells bounded by the lowest and highest row and column that has contained any data or formula or formatting since the last time that the workbook was saved.

    If you open a workbook and press Ctrl/End and the there appears to be empty rows or columns in the UsedRange, that usually means that one or more cells in the row and/or column of the selected cell contains some non-displaying data or formatting. For example, a cell may contain one or more blank characters, or possibly some conditional formatting.
    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insight into UsedRange (Excel 2003)

    Luckily, just using the UsedRange property in VBA resets the used range to the area that is truly in use. Of course the remarks of Legare do apply.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insight into UsedRange (Excel 2003)

    When did that start? AFAIK, using it in XL2K does not reset it, it is only reset when the workbook is saved.
    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insight into UsedRange (Excel 2003)

    I think in Excel 2002, so that won't help you.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insight into UsedRange (Excel 2003)

    Not until they remove the Registration/Activation Wizard.
    Legare Coleman

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insight into UsedRange (Excel 2003)

    > just using the UsedRange property in VBA resets the used range to the area that is truly in use
    I also thought that it had been fixed in 2002, but a simple test shows otherwise. Using 2003, SP2, I entered some data in B3:C4. I also wrote a one line macro:
    <pre>Sub Macro1()
    MsgBox ActiveSheet.UsedRange.Rows.Count
    End Sub
    </pre>

    Running the macro gives the correct answer: 2.

    But, if I select B4:C5 and press the $ (Currency Style) button, then the macro says that there are 3 rows in the used range. <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    It's also interesting that if I select all of the cells and press the $ button, then the macro says that there are two rows.

    Can you see if you get the same results in 2002?
    --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insight into UsedRange (Excel 2003)

    By formatting B5:C5 as currency, you have added them to the used range. Saving the workbook, even closing and reopening it will not remove them from the used range, even though they are empty. Formatted cells also contribute to the used range.

    If you create a new sheet and enter data in B3:C4, ActiveSheet.UsedRange.Rows.Count returns 2.
    Enter something in B5, and ActiveSheet.UsedRange.Rows.Count will return 3.
    Clear B5, and ActiveSheet.UsedRange.Rows.Count will return 2 again.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Insight into UsedRange (Excel 2003)

    Sam,
    I believe Excel is reasonably smart in that, if you apply formatting to an entire row or column, this does not affect the usedrange (I guess it assumes you are being lazy, as most of us are! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>) but if you apply it to particular cells, it regards that as a specific conscious decision, so those cells become part of the used range.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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