Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Range using value in cell
To all,
At the moment my macro uses the code below, so that the value in D10 changes the range selected. However is it possible to change bot the starting row number as well as the finish row number? Thanks Joseph myRg = "J2:J" & CStr(Range("D10")) Set ws = Sheets("FINAL Averages") Set r = Range(myRg) i.e if I enter 3 into D9, and 10 into D10 it will give the range J3:J10? Thanks for your help, Regards Joseph Crabtree |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Range using value in cell
Joseph,
Here's one way. Note that all the Cells and Range objects are relative to WS i.e. they are preceeded by a "." If you D9 & D10 values are not on that sheet, adjust accordingly. Dim WS As Worksheet Dim r As Range Set WS = Sheets("FINAL Averages") With WS Set r = .Range(.Cells(.Range("D9").Value, "J"), ..Cells(.Range("D10").Value, "J")) End With NickHK "joecrabtree" wrote in message oups.com... To all, At the moment my macro uses the code below, so that the value in D10 changes the range selected. However is it possible to change bot the starting row number as well as the finish row number? Thanks Joseph myRg = "J2:J" & CStr(Range("D10")) Set ws = Sheets("FINAL Averages") Set r = Range(myRg) i.e if I enter 3 into D9, and 10 into D10 it will give the range J3:J10? Thanks for your help, Regards Joseph Crabtree |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Range using value in cell
Hi Joseph
Something like this?? myRg = "J" & CStr(Range("D9")) & ":J" & CStr(Range("D10")) Set ws = Sheets("FINAL Averages") Set r = Range(myRg) Range(myRg).Select Regards Piet joecrabtree schreef: To all, At the moment my macro uses the code below, so that the value in D10 changes the range selected. However is it possible to change bot the starting row number as well as the finish row number? Thanks Joseph myRg = "J2:J" & CStr(Range("D10")) Set ws = Sheets("FINAL Averages") Set r = Range(myRg) i.e if I enter 3 into D9, and 10 into D10 it will give the range J3:J10? Thanks for your help, Regards Joseph Crabtree |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if then, change value of a cell range | New Users to Excel | |||
How to Run VBA Code on cell or range change | Excel Programming | |||
How do I change cell color based upon data range within the cell? | Excel Worksheet Functions | |||
How do I change a cell range with a reference cell? | Excel Discussion (Misc queries) | |||
Change cell colour for a cell or range within a predefined range | Excel Programming |