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