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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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