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 ?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try using .Clear rather than .ClearContents
 
Upvote 0
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?
 
Upvote 0
Try deleting all rows below your data & all columns to the right of your data, then save & close the workbook.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top