View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Mike Mike is offline
external usenet poster
 
Posts: 3,101
Default 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