how to reset the activesheet usedrange value

[Gary Keramidas]

if data is in a1:g50, a control end takes you to g50
if i want to clear this data, how do i reset the usedrange back to a1?

i have tried delete, clear and clearcontents, but a control end still takes
me to g50

 

[Dave Peterson]

Visit Debra Dalgleish's site for some techniques for resetting that
lastusedcell.
http://www.contextures.com/xlfaqApp.html#Unused

 

[Bob Phillips]

Unfortunately it is more complicated than that

Sub ResetUsedRange()
Dim iLastRow As Long
Dim iLastCol As Long
Dim rng As Range


With ActiveSheet
iLastRow = 0
iLastCol = 0
Set rng = .UsedRange
On Error Resume Next
iLastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
iLastCol = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
On Error GoTo 0

.Range(.Cells(iLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, iLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End With

End Su

 

[Gary Keramidas]

thanks dave. that resizes my macro buttons, too. i'm not going to worry
about resetting the range

 

[Dave Peterson]

Depending on what the buttons are, you can right click on each and choose Format
control and choose not to move or size with cells.

thanks dave. that resizes my macro buttons, too. i'm not going to worry
about resetting the range

 

[Gary Keramidas]

i already have them set like that and they still resized when i ran the code