how to reset the activesheet usedrange value
 

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

--


Gary

how to reset the activesheet usedrange value
 

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

Gary Keramidas wrote:

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

--

Gary

--

Dave Peterson

how to reset the activesheet usedrange value
 

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

--
Bob Phillip
-----------------------------------------------------------------------
Bob Phillips's Profile: http://www.excelforum.com/member.php...fo&userid=2695
View this thread: http://www.excelforum.com/showthread.php?threadid=47129

how to reset the activesheet usedrange value
 

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

--


Gary


"Dave Peterson" wrote in message
...
Visit Debra Dalgleish's site for some techniques for resetting that
lastusedcell.
http://www.contextures.com/xlfaqApp.html#Unused

Gary Keramidas wrote:

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

--

Gary

--

Dave Peterson

how to reset the activesheet usedrange value
 

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.



Gary Keramidas wrote:

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

--

Gary

"Dave Peterson" wrote in message
...
Visit Debra Dalgleish's site for some techniques for resetting that
lastusedcell.
http://www.contextures.com/xlfaqApp.html#Unused

Gary Keramidas wrote:

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

--

Gary

--

Dave Peterson

--

Dave Peterson

how to reset the activesheet usedrange value
 

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

--


Gary


"Dave Peterson" wrote in message
...
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.



Gary Keramidas wrote:

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

--

Gary

"Dave Peterson" wrote in message
...
Visit Debra Dalgleish's site for some techniques for resetting that
lastusedcell.
http://www.contextures.com/xlfaqApp.html#Unused

Gary Keramidas wrote:

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

--

Gary

--

Dave Peterson

--

Dave Peterson