Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range to VLOOKUP as a Variable (range in another file) | Excel Programming | |||
select range and put range address in variable | Excel Programming | |||
Macro to copy a specified range to a variable range | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |