Reset Excel Usedrange not working

[AbdulkareemAlhassni]

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

Activesheet.usedrange

and it doesnt seem to work

anyone have an idea ?

 

[Fluff]

Try using .Clear rather than .ClearContents

 

[AbdulkareemAlhassni]

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]

Try deleting all rows below your data & all columns to the right of your data, then save & close the workbook.

 

[Peter_SSs]

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.

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]

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]

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.

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]

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.

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]

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"

 

[JoeMo]

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.