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