Hi Norman -

Your procedure helps demonstrate that empty, formatted cells contribute to
the perception problem associated with resetting the UsedRange object. It
appears that resetting the usedrange with ActiveSheet.UsedRange functions
properly, but because empty formatted cells are included in the usedrange,
the reset can have unexpected consequences if a user is unaware of this.

As you stated in your first post, this manifests itself as a reliability
concern. Thanks for the discussion; for me, it has clarified prior
discussions on the issue.

---
Jay
(Student of the UsedRange)


"Norman Jones" wrote:

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