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.Hidden = 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
|
Similar Threads
