Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default refer to last cell in worksheet?

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" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default refer to last cell in worksheet?

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" wrote in message
...
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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
insert date Larry Excel Worksheet Functions 28 July 15th 06 02:41 AM
How do I get ONLY new info from 1 Worksheet to another automatical Elaine Excel Worksheet Functions 6 July 13th 06 05:45 PM
fill cell with color from other worksheet Crakel Excel Discussion (Misc queries) 4 April 19th 06 05:14 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
Copy from worksheet to another x times Union70 Excel Discussion (Misc queries) 0 March 7th 05 10:03 PM


All times are GMT +1. The time now is 05:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"