Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Reset the used range

  1. #1
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,676
    Over time, I've seen many a question about the annoyance of excel retaining the "old" used range settings when old data has been cleared from the worksheet.

    You know what I mean..... you once had 10,000 rows of data and deleted/cleared 5,000 rows, yet XL still scrolls to row 10,000 when you try to go to the bottom of the datarange.... annoying yes??

    This has been well documented around the XL world, but I don't think I've seen it here, other than in thread solutions....

    Anyway, any reference to the used range via VBA will reset the value to the current used range.

    So, set up a macro in your Personal.xls macro workbook & assign it a shortcut (for ease of use) and just use the following code....

    VB:
    Sub ResetRng() 
        ' Keyboard Shortcut: Ctrl+e 
        ' resets the used range of the activesheet 
        ActiveSheet.UsedRange 
    End Sub 
    
    
    Hope this helps,

    Will
    Kind Regards, Will Riley

    Web Presence:
    Personal: The Trouble With Data
    LinkedIn: Will Riley

  2. #2
    Join Date
    1st November 2004
    Location
    Wellington, New Zealand
    Posts
    84

    Further Info

    Hi Will, yes I get this question a lot too. I notice that sometimes, and I cant find any consistent cause, Activesheet.UsedRange doesnt always reset the usedrange. However, by including the Application qualifier it always works...

    VB:
    Application.ActiveSheet.UsedRange 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745
    ... and another take on the issue:

    http://www.j-walk.com/ss/excel/tips/tip73.htm
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  4. #4
    Join Date
    1st November 2004
    Location
    Wellington, New Zealand
    Posts
    84
    Cheers thanks Richie. Hows things? You seem to be on every Excel board in the planet.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745
    Hi parry,

    I'm good, thanks. You?

    Of course, you realise that in order for you to know that I seem to be on every Excel board on the planet then you must also ...
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. A Reset Button
    By dclark0699 in forum EXCEL HELP
    Replies: 1
    Last Post: June 28th, 2006, 11:24
  2. Reset to zero
    By andyjw in forum EXCEL HELP
    Replies: 10
    Last Post: June 6th, 2006, 04:34
  3. Reset Scheme
    By Runes in forum Excel and/or Access Help
    Replies: 1
    Last Post: May 8th, 2006, 23:13
  4. VBA : Reset The Used Range In A Spreadsheet
    By james_skeggs in forum EXCEL HELP
    Replies: 10
    Last Post: March 18th, 2004, 02:30
  5. VBA: reset checkboxes
    By xlite in forum Excel and/or Access Help
    Replies: 1
    Last Post: October 13th, 2003, 23:11

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
  •  
porno