Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
define range using col and row names; not cell name
How do you set a range using these four names? They define whole rows
and columns. The first column and row, along with the last column and row, define a range first_data_col last_data_col first_data_row last_data_row I can select columns with, as an example: mySheet.Range(mySheet.Range("first_data_col"), mySheet.Range("last_data_col")).EntireColumn.Hidde n = False I'm trying to do a sort, but I have to define the range to sort, as in ws.Range("D7:L25"), but I don't have cell names, I have 4 whole row and column names ( such as $K:$K, and $4:$4) I could disassemble these names and build a range with Cell, but isn't there a better way? I know this has got to be simple, but I just can’t seem to make it work. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
define range using col and row names; not cell name
Look at this:
FirstCol=mySheet.Range("first_data_Col").Column LastCol=mySheet.Range("last_data_Col").Column FirstRow=mySheet.Range("first_data_row").Row LastRow=mySheet.Range("last_data_row").row With MySheet set FilterRange= _ .range(.cells(FirstRow,FirstColumn),.cells(LastRow ,LastColumn)) End with Regards, Per On 27 Feb., 16:53, cate wrote: How do you set a range using these four names? They define whole rows and columns. *The first column and row, along with the last column and row, define a range first_data_col last_data_col first_data_row last_data_row I can select columns with, as an example: * * mySheet.Range(mySheet.Range("first_data_col"), mySheet.Range("last_data_col")).EntireColumn.Hidde n = False I'm trying to do a sort, but I have to define the range to sort, as in * * ws.Range("D7:L25"), but I don't have cell names, I have 4 whole row and column names ( such as $K:$K, and $4:$4) I could disassemble these names and build a range with Cell, but isn't there a better way? I know this has got to be simple, but I just can’t seem to make it work. Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
define range using col and row names; not cell name
One way:
Dim myRng as range dim First_Data_Row as range dim First_Data_Col as range Dim Last_Data_Row as range Dim Last_Data_Col as range with mySheet set first_data_row = .range("first_data_row") set last_data_row = .range("last_data_row") set first_data_col = .range("first_data_col") set last_data_col = .range("last_data_col") set myrng = .range(.cells(first_data_row.row, first_data_col.column), _ .cells(last_data_row.row, last_data_col.column)) end with You could drop the intermediate ranges and use: Dim myRng as range dim First_Data_Row as long dim First_Data_Col as long Dim Last_Data_Row as long Dim Last_Data_Col as long with mySheet first_data_row = .range("first_data_row").row last_data_row = .range("last_data_row").column first_data_col = .range("first_data_col").row last_data_col = .range("last_data_col").column set myrng = .range(.cells(first_data_row, first_data_col), _ .cells(last_data_row, last_data_col)) end with I like the first--If I want to use those ranges for anything else, I have them available. I like this syntax for the sort. I find it easier to understand and reuse: with myrng .cells.sort _ Key1:=.Columns(1), Order1:=xlAscending, _ key2:=.columns(3), order2:=xldescending, _ Header:=xlno, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom end with I don't have to worry about where that range is actually located on the sheet. (I do have to worry if the number of columns is ok <bg.) cate wrote: How do you set a range using these four names? They define whole rows and columns. The first column and row, along with the last column and row, define a range first_data_col last_data_col first_data_row last_data_row I can select columns with, as an example: mySheet.Range(mySheet.Range("first_data_col"), mySheet.Range("last_data_col")).EntireColumn.Hidde n = False I'm trying to do a sort, but I have to define the range to sort, as in ws.Range("D7:L25"), but I don't have cell names, I have 4 whole row and column names ( such as $K:$K, and $4:$4) I could disassemble these names and build a range with Cell, but isn't there a better way? I know this has got to be simple, but I just can’t seem to make it work. Thank you. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
define range using col and row names; not cell name
Thanks all!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Define Range Names in Excel 2003-SP2 | Excel Discussion (Misc queries) | |||
Define range by last cell in column? | Excel Programming | |||
Macro to define range names | Excel Programming | |||
define Range in vba using contents of a cell that is named | Excel Programming | |||
how to define range names | New Users to Excel |