Mike

Excels UsedRange is a little flaky. If the cells have ever had data then it
will believe they are still there no matter what.

If your data is contiguous you might check out .CurrentRegion or you could
check out .SpecialCells(xlCellTypeLastCell). Both may help and return a
range object

I prefer to use .End(xlUp) or .End(xlToLeft) assigning them to variables and
coming from a known last cell like so

Dim lLastRow as Long
Dim iLastColumn as Integer
lLastRow=Range("A65536").End(xlUp).Row
iLastColumn=Range("IV1").End(xlToLeft).Column

You can then use the variables to set the range. I find this 100% reliable
in XL97 up

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Mike" wrote in message
...
hi,

i am trying to establish the part of a worksheet that contains values.
the
usedrange function would seem to fit the bill (and works fine most of the
time), but every now and then it returns virtually the entire sheet as a
range rather than just the used part.

there is clearly something confusing it but there is no data obviously in
the cells it returns. does anyone have any ideas as to how to get around
this? is there some other way of finding the used part of a sheet?

the other thing i have noticed with the usedrange function is that when
all
the columns are returned (as per problem above) it then leaves the column
letters out of the range's address (if you look at the address property of
the range object).

any ideas would be welcome.

mike