define range using col and row names; not cell name

[cate]

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.

 

[Per Jessen]

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

 

[Dave Peterson]

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]

Thanks all!