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