Use variable for a range in WITH block
 

Greetings all. I have the following....

With Sheets("MacroDEA").Range("C2:" &
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
End With

It works fine. However, this is just one of many format changes I need to
make. Can I declare a range variable, set it = to the range in question,
then use the variable in the With block, so the code is not so cumbersome?
This is what I am trying, but it does not work

Dim MyRange as Range
MyRange = Sheets("MacroDEA").Range("C2:" &
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address)

With MyRange
.the rest of it
End with

Any ideas? I've also tried using a string and variant data types, but
nothing seems to work. Thank you.l

Use variable for a range in WITH block
 

Use

Set MyRange = Sheets(...).Range(...)

The "Set" is vital.

Sam

"Greg Snidow" wrote:

Greetings all. I have the following....

With Sheets("MacroDEA").Range("C2:" &
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
End With

It works fine. However, this is just one of many format changes I need to
make. Can I declare a range variable, set it = to the range in question,
then use the variable in the With block, so the code is not so cumbersome?
This is what I am trying, but it does not work

Dim MyRange as Range
MyRange = Sheets("MacroDEA").Range("C2:" &
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address)

With MyRange
.the rest of it
End with

Any ideas? I've also tried using a string and variant data types, but
nothing seems to work. Thank you.l

Use variable for a range in WITH block
 

Thank you Sam. I knew I had done it before, but I could not find it.
Anyhow, it works like a charm, and I'll never forget that again.

"Sam Wilson" wrote:

Use

Set MyRange = Sheets(...).Range(...)

The "Set" is vital.

Sam

"Greg Snidow" wrote:

Greetings all. I have the following....

With Sheets("MacroDEA").Range("C2:" &
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
End With

It works fine. However, this is just one of many format changes I need to
make. Can I declare a range variable, set it = to the range in question,
then use the variable in the With block, so the code is not so cumbersome?
This is what I am trying, but it does not work

Dim MyRange as Range
MyRange = Sheets("MacroDEA").Range("C2:" &
Sheets("MacroDEA").Cells(LastInputRow, ThetaCol - 1).Address)

With MyRange
.the rest of it
End with

Any ideas? I've also tried using a string and variant data types, but
nothing seems to work. Thank you.l