Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Reset the used range

  1. #1
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677
    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: Datasapien
    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

  6. #6
    Join Date
    1st May 2014
    Posts
    4

    Re: Reset the used range

    Another take on this issue: Do not bother about resetting the Used range, and just use the find method to search for the real last cell.

    It is actually a brilliant idea, search for "*" but set the order to xlPrevious. The find method wraps around the sheet to find the last used cell. We have to execute it byRows and byColumns to get the last Row and Column. Then we need to use the Worksheet.Cells(LastRow,LastColumn) to get the range.

    Here is an excerpt from a book in Microsoft's Dev Centre that talks about this issue: http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

    And in here you would find the same concept extended to find the first cell, and consequently the "Actual" used range: http://strugglingtoexcel.wordpress.c...nge-excel-vba/

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,525

    Re: Reset the used range

    A bit of shameless self promotion?

    Don't forget you can also help out on threads that aren't so old.

    Cheers,
    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  8. #8
    Join Date
    1st May 2014
    Posts
    4

    Re: Reset the used range

    Shameless indeed! Will do Ger, will start contributing soon.

    Excel Video Tutorials / Excel Dashboards Reports


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