Reset Excel Usedrange not working

AbdulkareemAlhassni

New Member
Joined
Nov 16, 2018
Messages
41
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 ?
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
Try using .Clear rather than .ClearContents
 

AbdulkareemAlhassni

New Member
Joined
Nov 16, 2018
Messages
41
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
Try deleting all rows below your data & all columns to the right of your data, then save & close the workbook.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,252
Office Version
365
Platform
Windows
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.
Rich (BB 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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,951
Office Version
2010
Platform
Windows
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,252
Office Version
365
Platform
Windows
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,951
Office Version
2010
Platform
Windows
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.

Rich (BB 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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,252
Office Version
365
Platform
Windows
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" :eek:
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,951
Office Version
2010
Platform
Windows
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" :eek:
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. :mad:
 

Watch MrExcel Video

Forum statistics

Threads
1,102,678
Messages
5,488,223
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top