Change Range using value in cell
 

To all,



At the moment my macro uses the code below, so that the value in D10
changes the range selected. However is it possible to change bot the
starting row number as well as the finish row number?

Thanks

Joseph


myRg = "J2:J" & CStr(Range("D10"))


Set ws = Sheets("FINAL Averages")


Set r = Range(myRg)




i.e if I enter 3 into D9, and 10 into D10 it will give the range
J3:J10?


Thanks for your help,


Regards


Joseph Crabtree

Change Range using value in cell
 

Joseph,
Here's one way. Note that all the Cells and Range objects are relative to WS
i.e. they are preceeded by a "."
If you D9 & D10 values are not on that sheet, adjust accordingly.

Dim WS As Worksheet
Dim r As Range

Set WS = Sheets("FINAL Averages")
With WS
Set r = .Range(.Cells(.Range("D9").Value, "J"),
..Cells(.Range("D10").Value, "J"))
End With

NickHK

"joecrabtree" wrote in message
oups.com...
To all,



At the moment my macro uses the code below, so that the value in D10
changes the range selected. However is it possible to change bot the
starting row number as well as the finish row number?

Thanks

Joseph


myRg = "J2:J" & CStr(Range("D10"))


Set ws = Sheets("FINAL Averages")


Set r = Range(myRg)




i.e if I enter 3 into D9, and 10 into D10 it will give the range
J3:J10?


Thanks for your help,


Regards


Joseph Crabtree

Change Range using value in cell
 

Hi Joseph

Something like this??

myRg = "J" & CStr(Range("D9")) & ":J" & CStr(Range("D10"))
Set ws = Sheets("FINAL Averages")
Set r = Range(myRg)
Range(myRg).Select

Regards

Piet

joecrabtree schreef:

To all,



At the moment my macro uses the code below, so that the value in D10
changes the range selected. However is it possible to change bot the
starting row number as well as the finish row number?

Thanks

Joseph


myRg = "J2:J" & CStr(Range("D10"))


Set ws = Sheets("FINAL Averages")


Set r = Range(myRg)




i.e if I enter 3 into D9, and 10 into D10 it will give the range
J3:J10?


Thanks for your help,


Regards


Joseph Crabtree