Before you answer Union(), read on...

I need a function to combine 2 ranges that return 1 range encompassing
both.

Given,
range1 = "A1:D10"
range2 = "C5:F23"

Union(range1, range2) would return "A1:D10,C5:F23", but I want
something that returns "A1:F23".

I could probably write something in a few minutes, but I prefer built-
in functions where possible.

Thanks,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

Range takes 2 arguments so something like this perhaps...

Dim rng As Range

Set rng = Range(Range("A1:D10"), Range("C5:F23"))
MsgBox rng.Address

--
HTH...

Jim Thomlinson


"Nick Hebb" wrote:

Before you answer Union(), read on...

I need a function to combine 2 ranges that return 1 range encompassing
both.

Given,
range1 = "A1:D10"
range2 = "C5:F23"

Union(range1, range2) would return "A1:D10,C5:F23", but I want
something that returns "A1:F23".

I could probably write something in a few minutes, but I prefer built-
in functions where possible.

Thanks,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

Thanks Jim -

Nice simple solution. I thought I needed to break apart the
constituent components (min row, max row, min column, max column) to
feed into Range. I'd be embarrassed to show the convoluted code I came
up with!

Thanks,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

One day we can compare embarassing code. I promise you it will be quite a
contest...
--
HTH...

Jim Thomlinson


"Nick Hebb" wrote:

Thanks Jim -

Nice simple solution. I thought I needed to break apart the
constituent components (min row, max row, min column, max column) to
feed into Range. I'd be embarrassed to show the convoluted code I came
up with!

Thanks,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

Not sure what you mean by "built-in functions" in this context, but how
about

Set rng3 = Range(rng1(1, 1), rng2(rng2.Rows.Count, rng2.Columns.Count))

Alan Beban

Nick Hebb wrote:
Before you answer Union(), read on...

I need a function to combine 2 ranges that return 1 range encompassing
both.

Given,
range1 = "A1:D10"
range2 = "C5:F23"

Union(range1, range2) would return "A1:D10,C5:F23", but I want
something that returns "A1:F23".

I could probably write something in a few minutes, but I prefer built-
in functions where possible.

Thanks,

Nick Hebb
BreezeTree Software
http://www.breezetree.com