You mean "the code on Deb's site" <g
Dave Peterson wrote:
Ahhh.
I thought you were saying that there was something wrong with Deb's code
(gasp!).
Leo Heuser wrote:
I know that, Dave.
My point was, that it might be possible to use
Set DummyRange = Sheets(SomeName).UsedRange
in connection with the *SpecialCells method* to reset
UsedRange for a sheet.
LeoH
"Dave Peterson" skrev i en meddelelse
...
Actually, Deb's code:
For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
is in the middle of a "for each" loop, so it'll get all the worksheets in
that
activeworkbook.
Leo Heuser wrote:
Hi Dave
I have not tested it thouroughly, but it looks like the line
Set DummyRange = Sheets(SomeName).UsedRange
resets UsedRange for the corresponding sheet.
Try this code:
Sub TestForLastCell()
'Leo Heuser, 25 Apr. 2004
'In an empty sheet enter data in J12 and M20
'and run TestForLastCell from that sheet
Dim Sh As Worksheet
Set Sh = ActiveSheet
'LastCell is M20
MsgBox "Last cell is " & Sh.Cells.SpecialCells(xlLastCell).Address
Sh.Rows("13:20").Delete
'LastCell should now be J12, but...
MsgBox "Last cell is still " &
Sh.Cells.SpecialCells(xlLastCell).Address
Call ResetUsedRange(Sh)
'UsedRange is reset and LastCell is correct (J12)
MsgBox "Last cell is now " &
Sh.Cells.SpecialCells(xlLastCell).Address
End Sub
Sub ResetUsedRange(Sh As Worksheet)
Dim DummyRange As Range
Set DummyRange = Sh.UsedRange
End Sub
--
Best Regards
Leo Heuser
Followup to newsgroup only please.
"Dave Peterson" skrev i en meddelelse
...
And sometimes hitting ctrl-end or using Leo's code will go further
down or
to
the right than you expected.
You can try to reset the last cell by using techniques at Debra
Dalgleish's
site:
http://www.contextures.com/xlfaqApp.html#Unused
--
Dave Peterson
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html