It works great... except...
What happens is that I need this to also work on each of 12 pages. In the
"Refers to" field of a new name, I tried entering in the formula
=OFFSET(SHEETNAME()!$A$1,0,0,COUNTA(SHEETNAME()!$A :$A),COUNTA(SHEETNAME()!$1:$1)
but that doesn't work.
Is there a way to have the Name calculate the range for the current sheet?

"Dave Peterson" wrote:

And row 1 always has something in it if that column is used?

Is it ok to use a name--you can either type the name into the namebox (to the
left of the formulabar).

In xl2003 menus:

Insert|Name|Define
Names in workbook: Sheet1!_all
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

This relies on data in column A and row 1 to always have something in it if the
corresponding row/column is used.

Then when you want to select the range from A1 to that last used cell:
F5 (or ctrl-g)
type
_all
and hit enter.

You can use any (legal) name you want. Try not to make it look like an address
in any version of excel (xl2007 has lots of columns, so a 3 letter name could
cause trouble when you upgrade to that version.)




Bob Arnett wrote:

Yes, the first cell in each row that is used always has something in it if
that row is used at all.

"Dave Peterson" wrote:

If you format a cell (not the entire column or entire row or entire sheet), then
excel will include that cell in the used range -- and use it when determining
the last used cell (shift-ctrl-end).

Is there any pattern to the data? Can you pick out a column that always has
data in it if the row is used? (Like if there's always something in column A
when that row is used.) And same thing for the rows?

Bob Arnett wrote:

I'm trying to select all cells from A1 to the last cell that has data. Using
Shift-Ctrl-End selects all cells including ones that just have borders and no
data. Is there a way to do this?

--

Dave Peterson


--

Dave Peterson