You can calculate the range like so

Set rng = Range(Range("A1"),Range("A1").End(xlDown))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RS" wrote in message
...
Dear Gord,

Thanks so much for the additional "sort" info, I did not know about
those limitations. I already have Autofilter enabled, but I guess in my
case, since I have hidden formulas within the rows that would be sorted,
that
simply checking the allow sort option in later versions wouldn't work.

When you said "Most everybody winds up with code to unprotect, sort,
then re-protect" would this macro account for increasing numbers of rows
as
additional info is added to the spreadsheet? If so, what is the code that
I
would use?

"Gord Dibben" wrote:

RS

Even in later versions of Excel there are restrictions on filtering and
sorting
that must be overcome.

The Protect Sheet dialog gives you a number of options to choose from,
but
autofilter must be enabled before protecting the sheet.

Sort will only work on rows in which all cells in the range of rows are
unprotected.

Most everybody winds up with code to unprotect, sort, then re-protect.


Gord Dibben MS Excel MVP

On Tue, 31 Oct 2006 21:58:02 -0800, RS
wrote:

Regarding the macro, since the number of rows needing to be sorted
would be
constantly changing, sounds like it might be easier to find a later
version
of Excel and then simply resave my spreadsheets using the new versions
ability to simply select a check box to allow sorting and
autofiltering.