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

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

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

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

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

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

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