No announcement yet.

Unconfigured Ad Widget


Reset the used range

  • Filter
  • Time
  • Show
Clear All
new posts

  • Reset the used range

    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....

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

    Kind Regards, Will Riley

    LinkedIn: Will Riley

  • #2
    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...



    • #3
      ... and another take on the issue:
      Cross-poster? Read this: Cross-posters
      Struggling to use tags (including Code tags)? : Forum tags


      • #4
        Cheers thanks Richie. Hows things? You seem to be on every Excel board in the planet.


        • #5
          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
            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:

            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/


            • #7
              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.


              Check out our new reputation system. Click on the "star" under the post!

              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
                Re: Reset the used range

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




                • cayusbonus
                  From week numbers to standard date format
                  Hi all, I am quite new in VBA, but after two weeks I have learnt a lot thanks to this forum, so I feel kind of in debt with it. I have been recently looking for an example where VBA converts from week dates to the regular mm/dd/yyyy date format, but I couldn't find a satisfactory answer. Yes, we have the weeknum function to be used in a spreadsheet but so far I couldn't call it in my VBA code. So therefore, I generated the code below that applies the ISO 8601 standard to make such a conversion. Hope it helps to somebody.

                  Sub WeeksToDates()
                  'Converting YYWW.DD to MM/DD/YYYY dates according ISO 8601. More info
                  Dim Todayf As String        'Date in yywk.dd format
                  Dim Yearf As Integer        'Year of the input date
                  1 day ago
                • Reafidy
                  Textbox Class Object
                  Hi All,

                  I recently had a request for a better method of controlling multiple textboxes on a userform. "I have lots of textboxes on a userform I want the user to only be able to enter numbers in all of them!"

                  The solution: Making use of a class module. The method below will alow you to handle the event for all textboxs at once without having to add events for each individual textbox on the form.

                  Add a class module and name it "clsObjHandler"

                  Option Explicit
                  Private WithEvents tbxCustom1 As MSForms.TextBox    'Custom Textbox
                  Public Property Set Control(tbxNew As MSForms.TextBox)
                      Set tbxCustom1 = tbxNew
                  End Property
                  Private Sub tbxCustom1_Change()
                  'Message Box To Display
                  December 11th, 2007, 05:32
                • Furybringerx
                  Parsing HTML Table to Excel
                  My personal project this past week has been trying to parse a HTML table to Excel.
                  I've used many resources to finally get the code I'm looking for. It has taken a week to figure it out so I feel that I should share it to help others like me.

                  Sub ParseTable()
                  Dim IE As InternetExplorer
                  Dim htmldoc As MSHTML.IHTMLDocument 'Document object
                  Dim eleColtr As MSHTML.IHTMLElementCollection 'Element collection for tr tags
                  Dim eleColtd As MSHTML.IHTMLElementCollection 'Element collection for td tags
                  Dim eleRow As MSHTML.IHTMLElement 'Row elements
                  Dim eleCol As MSHTML.IHTMLElement 'Column elements
                  Dim ieURL As String 'URL
                  'Open InternetExplorer
                  Set IE = CreateObject("InternetExplorer.Application")
                  December 11th, 2013, 05:39