Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert date | Excel Worksheet Functions | |||
How do I get ONLY new info from 1 Worksheet to another automatical | Excel Worksheet Functions | |||
fill cell with color from other worksheet | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) |