Variable within a range?
 

Does someone know how to insert a variable into a range.

For instance my original code is:
Sheets("Annual Data").Select
Range("BR7:CP7").Select
Selection.Copy
Range("BR8:CP25000").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

However, I don't want to copy down to row 25000, instead I have a variable
called maxRow. I would like to insert this into the code but I don't know how
to do this. The column would stay the same just the 25000 would be relplaced
with maxRow. The new code would look like

Sheets("Annual Data").Select
Range("BR7:CP7").Select
Selection.Copy
Range("BR8:CP[maxRow]").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Variable within a range?
 

This might work
maxRow= Range("CP" & Rows.Count).End(xlUp).Row
Range("BR8:CP" & maxRow).Select

mes C." wrote:

Does someone know how to insert a variable into a range.

For instance my original code is:
Sheets("Annual Data").Select
Range("BR7:CP7").Select
Selection.Copy
Range("BR8:CP25000").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

However, I don't want to copy down to row 25000, instead I have a variable
called maxRow. I would like to insert this into the code but I don't know how
to do this. The column would stay the same just the 25000 would be relplaced
with maxRow. The new code would look like

Sheets("Annual Data").Select
Range("BR7:CP7").Select
Selection.Copy
Range("BR8:CP[maxRow]").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Variable within a range?
 

You almost have it :-)
Old: Range("BR8:CP[maxRow]").Select
New: Range("BR8:CP" & maxRow).Select
If maxRow should be a Long data type variable.
--

Toby Erkson
http://excel.icbm.org/


"James C." wrote:

Does someone know how to insert a variable into a range.

For instance my original code is:
Sheets("Annual Data").Select
Range("BR7:CP7").Select
Selection.Copy
Range("BR8:CP25000").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

However, I don't want to copy down to row 25000, instead I have a variable
called maxRow. I would like to insert this into the code but I don't know how
to do this. The column would stay the same just the 25000 would be relplaced
with maxRow. The new code would look like

Sheets("Annual Data").Select
Range("BR7:CP7").Select
Selection.Copy
Range("BR8:CP[maxRow]").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False