Sub piuhpji()
ActiveSheet.UsedRange
End Sub

The above works fine for me in simple cases when I delete row 21 as
described below, in Excel 2000.
But I agree that there are a number of more complex cases when it wont work.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Rob van Gelder" wrote in message
...
I was pretty upset moving to 2000 finding that 'dummy =
UsedRange.Rows.Count' no longer worked.
In XL97, just accessing that property would reset the UsedRange. From 2000
onward it appears it's only ever reset on save.

This routine will find the last occupied cell within the UsedRange. Be
sure
to Activate the sheet you're after first.

Sub test()
Dim lngLastRow As Long, lngLastCol As Long

On Error Resume Next
lngLastRow = 1: lngLastCol = 1
With ActiveSheet.UsedRange
lngLastRow = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
lngLastCol = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
.Cells(lngLastRow, lngLastCol).Select
End With
End Sub


"Chelms" wrote in message
...
I have encountered the problem with a Worksheet that does not release
RAM
after a row delete that is discussed in Article Q163263. The solution
suggested in Q163263 does not work for Excel 9.0.2720.

Given 21 rows of data on a worksheet, if I delete the 21st row, then
run the macro:
Sub Reset_Range()
ActiveSheet.UsedRange
End Sub

Then I use the Edit Menu - Go To, Special, Last Cell
Excel 9 still selects the rightmost cell in row 21, should be row 20.
If I save the workbook, then the UsedRange property is reset, as it
was with Office 95.
Q163263 says the UsedRange property will release the unused RAM as of
Excel 97. It
doesn't. What happened between Office 97 and Office 2000?

I'm conducting these same operations using Autmation with MFC and
getting
the same results.