Dealing with Ranges

[Dean Goodmen]

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 :

 

[Frank Kabel]

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

 

[Dean Goodmen]

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?

 

[Myrna Larson]

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.

 

[Myrna Larson]

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.

 

[Myrna Larson]

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

 

[Dean Goodmen]

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..