Resetting Last Cell
 

I am a fond user of resetting last cell via the UsedRange property. My
favorite one is:

Sub ResetLastCel()
For each sh in Worksheets
sh.activate
x=Activesheet.UsedRange.Rows.count
Next
Sub

The above serves me well most of the time. There is however the odd 10%
(or so)chance of this failing and when it does, testing to locate the
last cell with *Cells.SpecialCells(xlCellTypeXlLastCell)* produces an
error. To convince myself that there are no false blank cells, I would
highlight and delete all rows beyond the "visible" usedrange.
Nothwithstanding this, the error persists.What gives?


David.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=473521

Resetting Last Cell
 

Try this one:

Function SetRealLastCell(shSheet As Worksheet) As Range
Set SetRealLastCell = Cells(Range(shSheet.Cells(1),
shSheet.UsedRange).Rows.count, _
Range(shSheet.Cells(1),
shSheet.UsedRange).Columns.count)
End Function

I think I got this from John Walkenbach's site.

RBS


"davidm" wrote in
message ...

I am a fond user of resetting last cell via the UsedRange property. My
favorite one is:

Sub ResetLastCel()
For each sh in Worksheets
sh.activate
x=Activesheet.UsedRange.Rows.count
Next
Sub

The above serves me well most of the time. There is however the odd 10%
(or so)chance of this failing and when it does, testing to locate the
last cell with *Cells.SpecialCells(xlCellTypeXlLastCell)* produces an
error. To convince myself that there are no false blank cells, I would
highlight and delete all rows beyond the "visible" usedrange.
Nothwithstanding this, the error persists.What gives?


David.


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=473521

Resetting Last Cell
 

See also
http://www.contextures.com/xlfaqApp.html#Unused


--
Regards Ron de Bruin
http://www.rondebruin.nl


"RB Smissaert" wrote in message ...
Try this one:

Function SetRealLastCell(shSheet As Worksheet) As Range
Set SetRealLastCell = Cells(Range(shSheet.Cells(1), shSheet.UsedRange).Rows.count, _
Range(shSheet.Cells(1), shSheet.UsedRange).Columns.count)
End Function

I think I got this from John Walkenbach's site.

RBS


"davidm" wrote in message
...

I am a fond user of resetting last cell via the UsedRange property. My
favorite one is:

Sub ResetLastCel()
For each sh in Worksheets
sh.activate
x=Activesheet.UsedRange.Rows.count
Next
Sub

The above serves me well most of the time. There is however the odd 10%
(or so)chance of this failing and when it does, testing to locate the
last cell with *Cells.SpecialCells(xlCellTypeXlLastCell)* produces an
error. To convince myself that there are no false blank cells, I would
highlight and delete all rows beyond the "visible" usedrange.
Nothwithstanding this, the error persists.What gives?


David.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=473521

Resetting Last Cell
 

Thanks for the tip.
What is the purpose though of dummyRng?
Souldn't you do With dummyRng after Set dummyRng?

RBS

"Ron de Bruin" wrote in message
...
See also
http://www.contextures.com/xlfaqApp.html#Unused


--
Regards Ron de Bruin
http://www.rondebruin.nl


"RB Smissaert" wrote in message
...
Try this one:

Function SetRealLastCell(shSheet As Worksheet) As Range
Set SetRealLastCell = Cells(Range(shSheet.Cells(1),
shSheet.UsedRange).Rows.count, _
Range(shSheet.Cells(1),
shSheet.UsedRange).Columns.count)
End Function

I think I got this from John Walkenbach's site.

RBS


"davidm" wrote in
message ...

I am a fond user of resetting last cell via the UsedRange property. My
favorite one is:

Sub ResetLastCel()
For each sh in Worksheets
sh.activate
x=Activesheet.UsedRange.Rows.count
Next
Sub

The above serves me well most of the time. There is however the odd 10%
(or so)chance of this failing and when it does, testing to locate the
last cell with *Cells.SpecialCells(xlCellTypeXlLastCell)* produces an
error. To convince myself that there are no false blank cells, I would
highlight and delete all rows beyond the "visible" usedrange.
Nothwithstanding this, the error persists.What gives?


David.


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread:
http://www.excelforum.com/showthread...hreadid=473521

Resetting Last Cell
 

To avoid the problem with merged cells I added a function that finds the
last merged cell:

Sub DeleteUnused(shSheet As Worksheet)

Dim lLR As Long
Dim lLC As Long
Dim wks As Worksheet
Dim rngDummy As Range
Dim arr

With shSheet

lLR = 0
lLC = 0

Set rngDummy = .UsedRange

On Error Resume Next
With rngDummy
lLR = _
.Cells.Find("*", _
after:=.Cells(1), _
LookIn:=xlFormulas, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
lLC = _
.Cells.Find("*", _
after:=.Cells(1), _
LookIn:=xlFormulas, _
lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
End With
On Error GoTo 0

'to avoid deleting a range that is part of a merged range
'--------------------------------------------------------
arr = getLastMergedCell(shSheet)

If arr(0) lLR Then
lLR = arr(0)
End If

If arr(1) lLC Then
lLC = arr(1)
End If

If lLR * lLC = 0 Then
.Columns.Delete
Else
.Range(.Cells(lLR + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, lLC + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With

End Sub


Function getLastMergedCell(shSheet As Worksheet) As Variant

'will give the row and column of the last merged cell in the sheet
'-----------------------------------------------------------------
Dim rngCell As Range
Dim rngMerge As Range
Dim arr(0 To 1) As Long

For Each rngCell In shSheet.UsedRange.Cells
Set rngMerge = rngCell.MergeArea
If rngCell.MergeCells Then
If rngCell.Row arr(0) Then
arr(0) = rngCell.Row
End If
If rngCell.Column arr(1) Then
arr(1) = rngCell.Column
End If
End If
Next

getLastMergedCell = arr

End Function


RBS


"Ron de Bruin" wrote in message
...
See also
http://www.contextures.com/xlfaqApp.html#Unused


--
Regards Ron de Bruin
http://www.rondebruin.nl


"RB Smissaert" wrote in message
...
Try this one:

Function SetRealLastCell(shSheet As Worksheet) As Range
Set SetRealLastCell = Cells(Range(shSheet.Cells(1),
shSheet.UsedRange).Rows.count, _
Range(shSheet.Cells(1),
shSheet.UsedRange).Columns.count)
End Function

I think I got this from John Walkenbach's site.

RBS


"davidm" wrote in
message ...

I am a fond user of resetting last cell via the UsedRange property. My
favorite one is:

Sub ResetLastCel()
For each sh in Worksheets
sh.activate
x=Activesheet.UsedRange.Rows.count
Next
Sub

The above serves me well most of the time. There is however the odd 10%
(or so)chance of this failing and when it does, testing to locate the
last cell with *Cells.SpecialCells(xlCellTypeXlLastCell)* produces an
error. To convince myself that there are no false blank cells, I would
highlight and delete all rows beyond the "visible" usedrange.
Nothwithstanding this, the error persists.What gives?


David.


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread:
http://www.excelforum.com/showthread...hreadid=473521

Resetting Last Cell
 

Sometimes, excel will "fix" the lastusedcell just by using .usedrange. The code
is just trying to reset that usedrange with a single command...(and continues
just in case it failed).

RB Smissaert wrote:

Thanks for the tip.
What is the purpose though of dummyRng?
Souldn't you do With dummyRng after Set dummyRng?

RBS

"Ron de Bruin" wrote in message
...
See also
http://www.contextures.com/xlfaqApp.html#Unused


--
Regards Ron de Bruin
http://www.rondebruin.nl


"RB Smissaert" wrote in message
...
Try this one:

Function SetRealLastCell(shSheet As Worksheet) As Range
Set SetRealLastCell = Cells(Range(shSheet.Cells(1),
shSheet.UsedRange).Rows.count, _
Range(shSheet.Cells(1),
shSheet.UsedRange).Columns.count)
End Function

I think I got this from John Walkenbach's site.

RBS


"davidm" wrote in
message ...

I am a fond user of resetting last cell via the UsedRange property. My
favorite one is:

Sub ResetLastCel()
For each sh in Worksheets
sh.activate
x=Activesheet.UsedRange.Rows.count
Next
Sub

The above serves me well most of the time. There is however the odd 10%
(or so)chance of this failing and when it does, testing to locate the
last cell with *Cells.SpecialCells(xlCellTypeXlLastCell)* produces an
error. To convince myself that there are no false blank cells, I would
highlight and delete all rows beyond the "visible" usedrange.
Nothwithstanding this, the error persists.What gives?


David.


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread:
http://www.excelforum.com/showthread...hreadid=473521





--

Dave Peterson

Resetting Last Cell
 

Thanks for all the replies. Having gone through the various variants of
applying the Usedrange property to reset the last cell, am I right to
conclude that the method is not 100% fail-safe?

The following code (from
http://www.contextures.com/xlfaqApp.html#Unused) raises that question.
Take a fresh Worksheet, and enter a figure in Range($H$58000). Clear the
cell and run the code. Result? The scroll bar stays down at
$H$58000!

To programatically reset the used range,

Note: This code may not work correctly if the worksheet contains merged
cells. To check your worksheet, you can run the TestForMergedCells code.



Sub DeleteUnused()


Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range


For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks

End Sub


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=473521

Resetting Last Cell
 

One of the warnings on Debra's site:

4. Save the file. Note: In older versions of Excel, you may have to Save, then
close and re-open the file before the used range is reset.

What version of excel are you using and did you save, close and reopen?



davidm wrote:

Thanks for all the replies. Having gone through the various variants of
applying the Usedrange property to reset the last cell, am I right to
conclude that the method is not 100% fail-safe?

The following code (from
http://www.contextures.com/xlfaqApp.html#Unused) raises that question.
Take a fresh Worksheet, and enter a figure in Range($H$58000). Clear the
cell and run the code. Result? The scroll bar stays down at
$H$58000!

To programatically reset the used range,

Note: This code may not work correctly if the worksheet contains merged
cells. To check your worksheet, you can run the TestForMergedCells code.



Sub DeleteUnused()


Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range


For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks

End Sub

--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=473521

--

Dave Peterson

Resetting Last Cell
 

I did what you said with H58000 in a new worksheet in a new workbook.

After clearing the cell, I when to the immediate window and did

Activesheet.usedRange
and hit enter

I then when to A1 in the sheet and the scrollbar was immediately normal. I
did Edit=Goto=Special and chose lastcell. The selection remained in A1.

xl2003

So worked fine for me.

--
Regards,
Tom Ogilvy

"davidm" wrote in
message ...

Thanks for all the replies. Having gone through the various variants of
applying the Usedrange property to reset the last cell, am I right to
conclude that the method is not 100% fail-safe?

The following code (from
http://www.contextures.com/xlfaqApp.html#Unused) raises that question.
Take a fresh Worksheet, and enter a figure in Range($H$58000). Clear the
cell and run the code. Result? The scroll bar stays down at
$H$58000!

To programatically reset the used range,

Note: This code may not work correctly if the worksheet contains merged
cells. To check your worksheet, you can run the TestForMergedCells code.



Sub DeleteUnused()


Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range


For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0

If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks

End Sub


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=473521