To all,

I have a macro that refers to a range eg:

Range.("P2:P100")

However I want to be able to change this range depending on what the
user enters in a cell ( A1). SO for example if the user enters '200'
the Range will be ("P2: P200"), or if the user enters 50 the range will
be ("P2:P50") etc.

Any help on this would be much appreciated. Thanks

Joseph Crabtree

Joe, try this:
Dim myRg As String
myRg = "P2:P" & CStr([A1])
James

joecrabtree wrote:
To all,

I have a macro that refers to a range eg:

Range.("P2:P100")

However I want to be able to change this range depending on what the
user enters in a cell ( A1). SO for example if the user enters '200'
the Range will be ("P2: P200"), or if the user enters 50 the range will
be ("P2:P50") etc.

Any help on this would be much appreciated. Thanks

Joseph Crabtree

Thanks for that. I have now got it to work.

using:

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

Set ws = Sheets("FINAL Averages")

Set r = Range(myRg)



I have another question, is it possible to change this so that I can
define both starting and finishing cells? I tried adding another string
but it didn't work. Any ideas?

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




On Dec 6, 3:20 pm, "Zone" wrote:
Joe, try this:
Dim myRg As String
myRg = "P2:P" & CStr([A1])
James



joecrabtree wrote:
To all,

I have a macro that refers to a range eg:

Range.("P2:P100")

However I want to be able to change this range depending on what the
user enters in a cell ( A1). SO for example if the user enters '200'
the Range will be ("P2: P200"), or if the user enters 50 the range will
be ("P2:P50") etc.

Any help on this would be much appreciated. Thanks

Joseph Crabtree- Hide quoted text -- Show quoted text -

Joe, there shouldn't be any problem with concatenating the range like
this:
myRg = "J" & CStr(Range("D3")) & ":J" & CStr(Range("D10"))
James
joecrabtree wrote:
Thanks for that. I have now got it to work.

using:

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

Set ws = Sheets("FINAL Averages")

Set r = Range(myRg)



I have another question, is it possible to change this so that I can
define both starting and finishing cells? I tried adding another string
but it didn't work. Any ideas?

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




On Dec 6, 3:20 pm, "Zone" wrote:
Joe, try this:
Dim myRg As String
myRg = "P2:P" & CStr([A1])
James



joecrabtree wrote:
To all,

I have a macro that refers to a range eg:

Range.("P2:P100")

However I want to be able to change this range depending on what the
user enters in a cell ( A1). SO for example if the user enters '200'
the Range will be ("P2: P200"), or if the user enters 50 the range will
be ("P2:P50") etc.

Any help on this would be much appreciated. Thanks

Joseph Crabtree- Hide quoted text -- Show quoted text -