Excel Combine Ranges

[Anne]

I have 4 different ranges in a worksheet and need to get the effective
box/area of all four together including he areas in between the ranges so
that there is on area to reference. What is the easiest way to do this.

E.g.

Range1: b2.c3
Range2: c20.d30
Range3: f8.g10
Range4: i30.k100

I need the resultant area reference to be b2:k100. Is there a quick way to
do this?
Thanks in advance.

 

[Norman Jones]

Hi Anne,

One way:

'=============>>
Public Sub Tester002()
Dim myRng As Range
Dim ar As Range

Set myRng = Range("B2:B3,C2030,F8:G10,I30:K100")

For Each ar In myRng.Areas
Set myRng = Range(myRng, ar)
Next ar

MsgBox myRng.Address(0, 0)
End Sub
'<<=============

 

[Herbert Seidenberg]

....or without VBA...
Insert > Name > Define
RangeAll Refers to: =Range1:Range2:Range3:Range4

 

[Anne]

thanks

Hi Anne,

One way:

'=============>>
Public Sub Tester002()
Dim myRng As Range
Dim ar As Range

Set myRng = Range("B2:B3,C2030,F8:G10,I30:K100")

For Each ar In myRng.Areas
Set myRng = Range(myRng, ar)
Next ar

MsgBox myRng.Address(0, 0)
End Sub
'<<=============