I looked in the excel help files but could not seem to find an
examples on Ranges and adding and removing ranges
from it. I was hoping someone here could someone make up a short
example for me.

1 I need a Ranged created that I can Add and Remove cells from
within any sub in my workbook.
2 Sample line to Add and remove a cell range. (Example :
TotalRange = TotalRange - My range)
3 Sample line to check if a range is contained in the range(
Example : Is MyRange in TotalRange.)

Thanks in advance :

Hi
1. for adding/combining ranges have a look at the Union method in vBA
dim rng as range
set rng=union(range1,range2)

2. For checking if they overlap use Intersect


--
Regards
Frank Kabel
Frankfurt, Germany

"Dean Goodmen" schrieb im Newsbeitrag
...
I looked in the excel help files but could not seem to find an
examples on Ranges and adding and removing ranges
from it. I was hoping someone here could someone make up a short
example for me.

1 I need a Ranged created that I can Add and Remove cells from
within any sub in my workbook.
2 Sample line to Add and remove a cell range. (Example :
TotalRange = TotalRange - My range)
3 Sample line to check if a range is contained in the range(
Example : Is MyRange in TotalRange.)

Thanks in advance :

HOw about removing a range of cells from a range?

Also the main range will be created on an OPEN event, will the ranges
be avaible to other sub if created there?


On Fri, 19 Nov 2004 20:02:59 +0100, "Frank Kabel"
wrote:

Hi
1. for adding/combining ranges have a look at the Union method in vBA
dim rng as range
set rng=union(range1,range2)

2. For checking if they overlap use Intersect

How about removing a range of cells from a range?

There's no built-in function for that. You'll have to do it brute-force.

Also the main range will be created on an OPEN event, will the ranges
be avaible to other sub if created there?

Only if you Dim the range variable as a public module-level variable,
preferably in a standard module.

HOw about removing a range of cells from a range?

Someone posted code for doing this some time within the last few months. I
believe the name of the routine was NonIntersect. You can probably find it via
a Google search.

Correction: the name of the procedure was NotIntersect, posted by Jim Wilcox
in August, 2004.


On Sat, 20 Nov 2004 11:32:26 -0500, Dean Goodmen wrote:

HOw about removing a range of cells from a range?

Also the main range will be created on an OPEN event, will the ranges
be avaible to other sub if created there?


On Fri, 19 Nov 2004 20:02:59 +0100, "Frank Kabel"
wrote:

Hi
1. for adding/combining ranges have a look at the Union method in vBA
dim rng as range
set rng=union(range1,range2)

2. For checking if they overlap use Intersect

On Sat, 20 Nov 2004 13:02:02 -0600, Myrna Larson
wrote:




Also the main range will be created on an OPEN event, will the ranges
be avaible to other sub if created there?

Only if you Dim the range variable as a public module-level variable,
preferably in a standard module.


Could you give me an example? I have it Dimed in my open event as
follows :

Public Sub Workbook_Open()
Dim DataRange As Range
Dim DataLine As Long
DataLine = 4
Set DataRange = Range(Cells(5, 2), Cells(5, 9))
Do
DataLine = DataLine + 1
Select Case ChkRow(DataLine)
Case Blank
Set DataRange = Union(DataRange, Range(Cells(DataLine, 2),
Cells(DataLine, 9)))
Exit Do
Case Full
Set DataRange = Union(DataRange, Range(Cells(DataLine, 2),
Cells(DataLine, 9)))
End Select
Loop


End Sub

BUt still does not reconise the the Range DataRange in Subs under this
one..







Correction: the name of the procedure was NotIntersect, posted by Jim Wilcox
in August, 2004.


On Sat, 20 Nov 2004 11:32:26 -0500, Dean Goodmen wrote:

HOw about removing a range of cells from a range?

Also the main range will be created on an OPEN event, will the ranges
be avaible to other sub if created there?


On Fri, 19 Nov 2004 20:02:59 +0100, "Frank Kabel"
wrote:

Hi
1. for adding/combining ranges have a look at the Union method in vBA
dim rng as range
set rng=union(range1,range2)

2. For checking if they overlap use Intersect

when you use "Dim DataRange as Range" within the workbook_open procedure, it
goes out of scope when that procedure ends.

You could put it in a General module dimmed as Public and it will be visible to
all the modules and procedures in your code.

Public DataRange as Range.

Take a look in VBA's help for Scope and Visibility.

You'll find that if you Dim it in the same module (outside any procedure), that
variable will be visible to any procedure within that module.


Dean Goodmen wrote:

On Sat, 20 Nov 2004 13:02:02 -0600, Myrna Larson
wrote:

Also the main range will be created on an OPEN event, will the ranges
be avaible to other sub if created there?

Only if you Dim the range variable as a public module-level variable,
preferably in a standard module.

Could you give me an example? I have it Dimed in my open event as
follows :

Public Sub Workbook_Open()
Dim DataRange As Range
Dim DataLine As Long
DataLine = 4
Set DataRange = Range(Cells(5, 2), Cells(5, 9))
Do
DataLine = DataLine + 1
Select Case ChkRow(DataLine)
Case Blank
Set DataRange = Union(DataRange, Range(Cells(DataLine, 2),
Cells(DataLine, 9)))
Exit Do
Case Full
Set DataRange = Union(DataRange, Range(Cells(DataLine, 2),
Cells(DataLine, 9)))
End Select
Loop


End Sub

BUt still does not reconise the the Range DataRange in Subs under this
one..

Correction: the name of the procedure was NotIntersect, posted by Jim Wilcox
in August, 2004.


On Sat, 20 Nov 2004 11:32:26 -0500, Dean Goodmen wrote:

HOw about removing a range of cells from a range?

Also the main range will be created on an OPEN event, will the ranges
be avaible to other sub if created there?


On Fri, 19 Nov 2004 20:02:59 +0100, "Frank Kabel"
wrote:

Hi
1. for adding/combining ranges have a look at the Union method in vBA
dim rng as range
set rng=union(range1,range2)

2. For checking if they overlap use Intersect

--

Dave Peterson