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