Turns out now that in Excel 2000 SP-3, simply calling the UsedRange property
seems to reset the used range. I don't know how well this routine works in
newer versions. Debra Dalgleish's approach (also seen on the MSDN web site)
used to be what you had to use in previous versions.

Public Sub ResetUsedRange()
ActiveSheet.UsedRange
End Sub

You are 100% correct about the CurrentRegion property returning only
contiguous cells. It must be used carefully. There used to be side effects
with it, which seem to be gone now, and I cannot remember exactly what they
were.
--
Regards,
Bill


"Dave Peterson" wrote in message
...
I agree with you in general. But lots of times, I'll know that there's a
column
that has to be filled in (some sort of key).

And if I know the data well enough, I'll use that method.

But if I don't know the data (some generic routine), I'll use the lastcell
(after trying to reset the usedrange).

I don't like the currentregion for the same reason you don't like the
.end(xlup).row. If there's gaps in the columns/rows, you might not get
what you
really want.

(I like Debra Dalgleish's approach to find that real last used cell:
http://www.contextures.com/xlfaqApp.html#Unused)

Bill Renaud wrote:

"Dave Peterson" wrote:
<<If your data were in A99:A100, you'd get 2.

True, but then I was assuming that the application was for a list, since
this is where this technique usually shows up. Most people don't leave
the
first 98 rows of a worksheet totally blank. (Unless they are some of the
posters to this NG! Grin! I try to teach good design principles rather
than
giving the instant answer!)

Most of the other suggestions that were using some variation of
.End(xlUp).Row have the problem that not all columns will have complete
data. Say I have a table 5 columns wide and 100 rows deep (starting in
cell
$A$1, of course!). If $A$100 happens to be blank, then any method
counting
the number of rows of data only in column $A will return only 99, when
the
100th row actually has data (in columns $B:$E). I guess that is why I
always
use UsedRange, or CurrentRegion, or some variation of those properties,
when
working with a list.
--
Regards,
Bill

--

Dave Peterson