Reset Excel Usedrange not working
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Reset Excel Usedrange not working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Reset Excel Usedrange not working

    Hey guys,

    i have been trying to reset my excel range after using clear content but everytime i use it the range stays the same.

    anyone have an idea how to force the range to get back to "Used range" after " clearing Content "

    i have used CTRL + END, yet all cells are blank and nothing inbetween the start and the end, am sure of it.

    currently am using the below code

    Code:
    Activesheet.usedrange
    and it doesnt seem to work

    anyone have an idea ?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,051
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Reset Excel Usedrange not working

    Try using .Clear rather than .ClearContents
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Reset Excel Usedrange not working

    Quote Originally Posted by Fluff View Post
    Try using .Clear rather than .ClearContents
    still not working, my excel sheet is up to row 103000 DZ.

    when i clear it deletes even the format

    yet i have my scroll bar scrolling till 103000, excel still reads the empty cells as if its has content.


    any other solution?

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,051
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Reset Excel Usedrange not working

    Try deleting all rows below your data & all columns to the right of your data, then save & close the workbook.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,491
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Reset Excel Usedrange not working

    There could certainly be an issue with Clear v ClearContents but I suspect the main issue is that you need to Save the file (as suggested by Fluff) after the Clear/ClearContents. There should be no need to close the workbook though.
    To test, take a fresh workbook and save it as a macro-enabled workbook with a blank active sheet.

    Now put this code in a module in the workbook and step through it with the F8 key, observing what happens on the sheet after each line of code.
    Code:
    Sub UsedRange_Example()
      With ActiveSheet
        .Range("A1:J10").Value = "x"
        .Cells.SpecialCells(xlCellTypeLastCell).Select  'J10 should be selected
        .Range("A8:J10").ClearContents                  'Data cleared from rows 8:10
        .Cells.SpecialCells(xlCellTypeLastCell).Select  'J10 still gets selected
        .Parent.Save
        .Cells.SpecialCells(xlCellTypeLastCell).Select  'J7 should now be selected
      End With
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,393
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Reset Excel Usedrange not working

    ClearContents clears the contents, but not the formatting. In Peter's example, if A8:J10 is manually filled with a color and ClearContents is used, J10 will still be selected in the last step just before the End With line. If Clear is used then the color fill will be removed too, and J7 is selected in the last step.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,491
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Reset Excel Usedrange not working

    Quote Originally Posted by JoeMo View Post
    ClearContents clears the contents, but not the formatting. In Peter's example, if A8:J10 is manually filled with a color and ClearContents is used, J10 will still be selected in the last step just before the End With line. If Clear is used then the color fill will be removed too, and J7 is selected in the last step.
    True - but just to be clear, I wasn't suggesting ClearContents as the best method, rather that most likely the OP's problem was not saving.

    Having said that, using Clear and saving or even clear, save, close & reopen does not always reset the used range.
    Here is another example to try on a fresh sheet.

    Code:
    Sub UsedRange_Example_2()
      With ActiveSheet
        .Range("K1:K15").FormatConditions.Add Type:=xlExpression, Formula1:="=K1<>"""""
        .Range("K1:K15").FormatConditions(1).Interior.Color = vbBlue
        .Range("A1:K15").Value = "x"                    'K1:K15 goes blue
        .Cells.SpecialCells(xlCellTypeLastCell).Select  'K15 should be selected
        .Range("A8:K15").Clear                          'Data & CF cleared from rows 8:15
    '    .Rows("8:15").Delete                            'Remove rows 8:15 entirely
        .Range("A1").Select                             'Just to move the selection away from K15
        .Cells.SpecialCells(xlCellTypeLastCell).Select  'K15 still gets selected
        .Range("A1").Select                             'Just to move the selection away from K15
        .Parent.Save
        .Cells.SpecialCells(xlCellTypeLastCell).Select  'K15 STILL gets selected
      End With
    End Sub
    Even if I save, close & reopen this workbook Ctrl+End still takes me to K15.

    However, if the 'Clear' line of code is replaced by the commented out one below it, then the xlCellTypeLastCell, Ctrl+End and UsedRange all immediately recognise K7 as the last cell

    The upshot is that I am always fairly wary about trusting .SpecialCells(xlCellTypeLastCell) or UsedRange
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,393
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Reset Excel Usedrange not working

    Quote Originally Posted by Peter_SSs View Post
    True - but just to be clear, I wasn't suggesting ClearContents as the best method, rather that most likely the OP's problem was not saving.

    Having said that, using Clear and saving or even clear, save, close & reopen does not always reset the used range.
    Here is another example to try on a fresh sheet.

    Code:
    Sub UsedRange_Example_2()
      With ActiveSheet
        .Range("K1:K15").FormatConditions.Add Type:=xlExpression, Formula1:="=K1<>"""""
        .Range("K1:K15").FormatConditions(1).Interior.Color = vbBlue
        .Range("A1:K15").Value = "x"                    'K1:K15 goes blue
        .Cells.SpecialCells(xlCellTypeLastCell).Select  'K15 should be selected
        .Range("A8:K15").Clear                          'Data & CF cleared from rows 8:15
    '    .Rows("8:15").Delete                            'Remove rows 8:15 entirely
        .Range("A1").Select                             'Just to move the selection away from K15
        .Cells.SpecialCells(xlCellTypeLastCell).Select  'K15 still gets selected
        .Range("A1").Select                             'Just to move the selection away from K15
        .Parent.Save
        .Cells.SpecialCells(xlCellTypeLastCell).Select  'K15 STILL gets selected
      End With
    End Sub
    Even if I save, close & reopen this workbook Ctrl+End still takes me to K15.

    However, if the 'Clear' line of code is replaced by the commented out one below it, then the xlCellTypeLastCell, Ctrl+End and UsedRange all immediately recognise K7 as the last cell

    The upshot is that I am always fairly wary about trusting .SpecialCells(xlCellTypeLastCell) or UsedRange
    Hmmm, for me K7 gets selected after the Save, not K15. This happens whether I step through the code or just run it.

    And, as an aside, because of the CF rule your code contains, K7 is also selected if I replace Clear with ClearContents as the clear contents for cells K8:K15 removes the CF fill from those cells.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,491
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Reset Excel Usedrange not working

    Quote Originally Posted by JoeMo View Post
    Hmmm, for me K7 gets selected after the Save, not K15.
    Thanks for that info Joe. I guess that differing behaviour just reinforces for me "I am always fairly wary about trusting .SpecialCells(xlCellTypeLastCell) or UsedRange"
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,393
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Reset Excel Usedrange not working

    Quote Originally Posted by Peter_SSs View Post
    Thanks for that info Joe. I guess that differing behaviour just reinforces for me "I am always fairly wary about trusting .SpecialCells(xlCellTypeLastCell) or UsedRange"
    I've never used .SpecialCells(xlCellTypeLastCell) as, early on in my quest to learn VBA for Excel, I read several reports that it is not reliable. I'm disappointed now to learn that UsedRange may not be entirely reliable either. I use it quite often to respond to posts that provide too little information on layout.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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