MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Replace Range with variable


Posted by Carl on January 28, 2001 10:38 PM

Say you have a range like:
Range("C9,E9,G9,I9,K9,M9,O9,Q9,S9,U9,").Select
is it possible to replace the Row numbers with a variable name like MyRow, and then MyRow can be set according to a formula.


Posted by Dave Hawley on January 29, 2001 12:48 AM


Hi Carl

You could use this:

Dim Rw As Long

Rw = 10
Range("C" & Rw & ",E" & Rw & ",G" & Rw & _
" ,I" & Rw & ",K" & Rw & ",M" & Rw & ",O" & Rw _
& ",Q" & Rw & ",S" & Rw & ",T" & Rw).Select

Dave

OzGrid Business Applications

Posted by Carl on January 29, 2001 2:25 AM

THANK YOU Dave, I have been struggling over that for two weeks now and everything I tried kept erroring out on me.

Posted by Carl on January 29, 2001 3:24 PM


It Didn't work.

Posted by Dave Hawley on January 30, 2001 4:34 AM


Hi Carl

It should work, no reason why it shouldn't.

Paste this in as is, just delete the occurences of :

Sub a()
Dim Rw As Long
Rw = 10
Range("C" & Rw & ",E" & Rw & ",G" & Rw & _
" ,I" & Rw & ",K" & Rw & ",M" & Rw & ",O" & Rw _
& ",Q" & Rw & ",S" & Rw & ",T" & Rw).Select
End Sub

Dave

OzGrid Business Applications