Hi Jay,

The methods for resetting the UsedRange may be version
dependent.

If you perform a Google search of the NG archives, you will
find considerable previous discussion.

Perhaps, however, try:

'=============
Public Sub Tester()
Dim SH As Worksheet
Dim Rng As Range

Set SH = ActiveSheet

With SH
Set Rng = .Cells(Rows.Count, Columns.Count)

With Rng
.NumberFormat = "dd\mm\yy"
.Clear
End With

MsgBox .UsedRange.Address(0, 0)
End With
End Sub
'<<=============

As previously indicated, I believe that there may be more
reliable ways of establishing the data range than the potentially
problematic UsedRange property.


---
Regards,
Norman


"Jay" wrote in message
...
Hi Norman -

Programmatically referencing the UsedRange appears to automatically
disregard the "high-water mark" left by cleared cells that are located
beyond
the data range. The VB UsedRange property returns the 'data-only'
UsedRange
even when the UI does not, i.e., when Ctrl-End senses an empty high water
mark.

In fact, try this out as a one-line alternative to Debra's DeleteUnused
procedure to reset the UsedRange after clearing cells to the right and
below
a data range:

Activesheet.UsedRange.Select

Once this statement executes, the UsedRange is reset (check it in the UI
with Ctrl-End). One advantage of this approach is that it's
non-destructive;
formulas in the data range that reference rows outside the data range are
not
converted to "#Ref!"

I haven't been able to make this statement fail (yet) to reset the
UsedRange
or fail to directly return the correct data range. I have a theory that
perceived unpredictability may be an artifact of the sensitivity of the UI
to
the high water mark; the VB UsedRange property appears to disregard it.

---
Excel2003/WinXP
Jay