As the owner said to me when I was walking through the China shop: You break
it, you bought it.

And that's no bull!



Debra Dalgleish wrote:

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

--

Dave Peterson