Resize Dynamic Ranges
 

Or, you could just make them dynamic where the second one depended on the
first
rng1=offset($A$2,0,0,counta($a:$a),4)
rng2=offset($h$2,0,0,counta($a:$a),4)
notice I changed the range from A to H but still counted rows in A.
--
Don Guillett
SalesAid Software

"VJ" wrote in message
...
I have two ranges rng1 and rng2. These are two dynamic ranges. I need to
resize the rng1 according to rng2.

How can I set the size of rng1 to rng2 through VBA code?

Looking for help.

Thanks

Resize Dynamic Ranges
 

Or

rng2 = offset(rng1,0,7)

to make rng 2 the same size, offset by 7 columns (A to H)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Don Guillett wrote:

Or, you could just make them dynamic where the second one depended on the
first
rng1=offset($A$2,0,0,counta($a:$a),4)
rng2=offset($h$2,0,0,counta($a:$a),4)
notice I changed the range from A to H but still counted rows in A.

Resize Dynamic Ranges
 

Jon,
I never thought of that one. Isn't it great that you can do the same thing
in so many different ways.

Don Guillett
SalesAid Software

"Jon Peltier" wrote in message
...
Or

rng2 = offset(rng1,0,7)

to make rng 2 the same size, offset by 7 columns (A to H)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Don Guillett wrote:

Or, you could just make them dynamic where the second one depended on
the
first
rng1=offset($A$2,0,0,counta($a:$a),4)
rng2=offset($h$2,0,0,counta($a:$a),4)
notice I changed the range from A to H but still counted rows in A.

Resize Dynamic Ranges
 

Don -

This is a good one for defining the data for a chart. Define your X
values, then Y1 is offset(X,0,1), Y2 is offset(X,0,2), etc. When I first
came up with it I was just glad to eliminate all the typing in the tiny
Define Names box, but I've since realized how it also improves the
uniformity of the data ranges.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Don Guillett wrote:

Jon,
I never thought of that one. Isn't it great that you can do the same thing
in so many different ways.

Don Guillett
SalesAid Software

"Jon Peltier" wrote in message
...

Or

rng2 = offset(rng1,0,7)

to make rng 2 the same size, offset by 7 columns (A to H)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Don Guillett wrote:


Or, you could just make them dynamic where the second one depended on

the

first
rng1=offset($A$2,0,0,counta($a:$a),4)
rng2=offset($h$2,0,0,counta($a:$a),4)
notice I changed the range from A to H but still counted rows in A.

Resize Dynamic Ranges
 

Jon,

If you aren't aware of this idea maybe it will help for all the typing.
Just select one you have already donetype in a new nameselect the refers
to box<F2edit only what is necessary. This is how I usually do it so never
stumbled across your method.

--
Don Guillett
SalesAid Software

"Jon Peltier" wrote in message
...
Don -

This is a good one for defining the data for a chart. Define your X
values, then Y1 is offset(X,0,1), Y2 is offset(X,0,2), etc. When I first
came up with it I was just glad to eliminate all the typing in the tiny
Define Names box, but I've since realized how it also improves the
uniformity of the data ranges.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Don Guillett wrote:

Jon,
I never thought of that one. Isn't it great that you can do the same
thing
in so many different ways.

Don Guillett
SalesAid Software

"Jon Peltier" wrote in message
...

Or

rng2 = offset(rng1,0,7)

to make rng 2 the same size, offset by 7 columns (A to H)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Don Guillett wrote:


Or, you could just make them dynamic where the second one depended on

the

first
rng1=offset($A$2,0,0,counta($a:$a),4)
rng2=offset($h$2,0,0,counta($a:$a),4)
notice I changed the range from A to H but still counted rows in A.

Resize Dynamic Ranges
 

Don -

Yep, I've done that too. Laziness is the mother of invention.

- Jon

Don Guillett wrote:

Jon,

If you aren't aware of this idea maybe it will help for all the typing.
Just select one you have already donetype in a new nameselect the refers
to box<F2edit only what is necessary. This is how I usually do it so never
stumbled across your method.