refer to last cell in worksheet?

I'd like to find a generic way to specify the entire data range in a
worksheet (without knowing how many rows or columns are in the worksheet).

Is there a way to refer to the last cell in a worksheet (not just a specific
row or column)? I see how one can use GO TO... to select the last cell, but
I can't find a way to refer to the last cell in a formula.

Alternatively, is there an easy way to identify the last (rightmost)
non-blank column in a worksheet? I see how COUNTA could be used to identify
a non-blank column, but is there a way to identify the last non-blank column,
or at least the first?

Basically, I'd like to use INDEX in one worksheet to find data in another
worksheet and don't know how to specify a generic range that will work
regardless of the number of columns (the number of rows is much less
important).

Thanks in advance.

If you're using VBA then this might help:
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
-this way you will select entire range which has data in row 1.
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
-this way you will select entire range which has data in column A.

But if you need huge area because there is great table than this code miught
help:
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
-this way you have selected complete table (no matter how many rows or
columns are there)
and if you need to tell excel that this range has name (for use in functions
or for copy or cur...):
Dim some_range As Range
Set some_range = Selection


Now, you can do whatever you want with range name "some_range"...

"svh646" <[email protected]> wrote in message
news:[email protected]...
> I'd like to find a generic way to specify the entire data range in a
> worksheet (without knowing how many rows or columns are in the worksheet).
>
> Is there a way to refer to the last cell in a worksheet (not just a
> specific
> row or column)? I see how one can use GO TO... to select the last cell,
> but
> I can't find a way to refer to the last cell in a formula.
>
> Alternatively, is there an easy way to identify the last (rightmost)
> non-blank column in a worksheet? I see how COUNTA could be used to
> identify
> a non-blank column, but is there a way to identify the last non-blank
> column,
> or at least the first?
>
> Basically, I'd like to use INDEX in one worksheet to find data in another
> worksheet and don't know how to specify a generic range that will work
> regardless of the number of columns (the number of rows is much less
> important).
>
> Thanks in advance.

Thanks, Sasa. I was trying to stay away from VBA on this one and was looking
for an easy way to specify all the data in a worksheet for an INDEX command,
but I think I may be better off taking a different approach.


"Sasa Stankovic" wrote:

> If you're using VBA then this might help:
> Range("A1").Select
> Range(Selection, Selection.End(xlToRight)).Select
> -this way you will select entire range which has data in row 1.
> Range("A1").Select
> Range(Selection, Selection.End(xlDown)).Select
> -this way you will select entire range which has data in column A.
>
> But if you need huge area because there is great table than this code miught
> help:
> Range("A1").Select
> Range(Selection, Selection.End(xlToRight)).Select
> Range(Selection, Selection.End(xlDown)).Select
> -this way you have selected complete table (no matter how many rows or
> columns are there)
> and if you need to tell excel that this range has name (for use in functions
> or for copy or cur...):
> Dim some_range As Range
> Set some_range = Selection
>
>
> Now, you can do whatever you want with range name "some_range"...
>
> "svh646" <[email protected]> wrote in message
> news:[email protected]...
> > I'd like to find a generic way to specify the entire data range in a
> > worksheet (without knowing how many rows or columns are in the worksheet).
> >
> > Is there a way to refer to the last cell in a worksheet (not just a
> > specific
> > row or column)? I see how one can use GO TO... to select the last cell,
> > but
> > I can't find a way to refer to the last cell in a formula.
> >
> > Alternatively, is there an easy way to identify the last (rightmost)
> > non-blank column in a worksheet? I see how COUNTA could be used to
> > identify
> > a non-blank column, but is there a way to identify the last non-blank
> > column,
> > or at least the first?
> >
> > Basically, I'd like to use INDEX in one worksheet to find data in another
> > worksheet and don't know how to specify a generic range that will work
> > regardless of the number of columns (the number of rows is much less
> > important).
> >
> > Thanks in advance.
>
>
>