Use variable for a range in WITH block

G

Greg Snidow

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
 
G

Greg Snidow

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top