Copy A Dynamically Range Depending On Last Filled Non-Empty Cell In That Column

[Bill Wendt]

I am trying to use a variable in a range of cells. IE Range(a2:z100).select... I would like to set something like MyRange=1000 and set use Range(a2:Myrange).select. that way when MyRange changes i can make 1 change in the Macro and still have it run like i want.

[bakerman]

What is it you're trying to do because in 99% of the cases it isn't necessary to SELECT a range when writing code.
It's obselete and slows down the execution.
You don't write

Range("A2:Z1000").Select
Selection.Copy
Sheets("DestinationSheet").Select
Range("A1").Select
Selection.Paste

when copying a range but

Range("A2:Z1000").Copy Sheets("DestinationSheet").Range("A1")

So when determining the address of an entire Range you write something like

With Sheets("Sheet1")
    .Range("A2:Z" & .Cells(.Rows.Count, 1).End(xlUp).Row).Copy Sheets("DestinationSheet").Range("A1")
End With

where 1 stands for the columnnumber in which in every row always a value is entered.

[Bill Wendt]

Thanks for the quick response
i will play with that. :-)