Select range of cells from A1 to last cell with data?

[Bob Arnett]

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?

 

[Sean Timmons]

that indicates to me the cells with borders also have somethign in them. Most
likely a null set. Ifyou highlight from last actual data row to bottom of
your blank cset and click the delete button, you will be rid of them.

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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),COUNTA(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]

That looks like it will work for me but I'll have to play around with it to
be sure. Thanks for the info.

 

[Bob Arnett]

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]

You could, but the formula will get really ugly, really fast (well, for me,
anyway).

Instead try this:
Delete those worksheet level names (all of them!). Sorry.

Now define a workbook level name:

Insert|Name|Define
Names in workbook: _all (don't include the sheet name here)
Use this formula
Refers to:
=OFFSET(indirect("A1"),0,0,COUNTA(indirect("A:A")),COUNTA(indirect("1:1")))

Then try it on a couple of sheets--remember column A and row 1 can't have empty
cells. But even a formula like: ="" (that makes the cell look empty, but it's
not) is ok.

If you want to see how you can return the sheet name to a cell (or in a formula
like this), take a look at Debra Dalgleish's site:

http://contextures.com/xlfaqFun.html#SheetName

ps. If you have a worksheet that doesn't follow the rules, but you don't need
this technique, the name is still valid and will refer to a different range--but
just ignore that sheet.

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?

 

[Bob Arnett]

Thanks. Works perfectly!

You could, but the formula will get really ugly, really fast (well, for me,
anyway).

Instead try this:
Delete those worksheet level names (all of them!). Sorry.

Now define a workbook level name:

Insert|Name|Define
Names in workbook: _all (don't include the sheet name here)
Use this formula
Refers to:
=OFFSET(indirect("A1"),0,0,COUNTA(indirect("A:A")),COUNTA(indirect("1:1")))

Then try it on a couple of sheets--remember column A and row 1 can't have empty
cells. But even a formula like: ="" (that makes the cell look empty, but it's
not) is ok.

If you want to see how you can return the sheet name to a cell (or in a formula
like this), take a look at Debra Dalgleish's site:

http://contextures.com/xlfaqFun.html#SheetName

ps. If you have a worksheet that doesn't follow the rules, but you don't need
this technique, the name is still valid and will refer to a different range--but
just ignore that sheet.