How to exclude certain cells from a range?

[Ed]

I have a range set to a group of cells (say A1:E5) -call it rngSquare. I've
iterated through this and picked out say 15 non-contiguous cells to create
another range - call it rngWork. Now let's throw in ranges for each row of
the square - rngRow1 - rngRow5.

I know I can intersect and union these ranges (such as: every cell in
rngRow1 that is also in rngWork) to narrow down a search-and-select mission.
But say I want to search every cell in rngWork **except** rngRow1. Do I
have to loop through all the ranges like:
Union(Intersect(rngWork, rngRow2), Intersect(rngWork, rngRow3), etc . . .
or is there an easier way to do something like:
Intersect(rngWork, Not rngRow1)???

Ed

 

[Norman Jones]

Hi Ed,

See:

http://tinyurl.com/agpz9

 

[Ed]

Norman:

From the first post, I felt the deep waters closing over my head!! 8>)
I gleaned a bit from the first couple of posts - after that, it went far
beyond me. (Like my teenagers listening to me!) So I think I will take the
general assumption that yes, it can be done, but not by me at this point,
and plod my "kludgy" way through this until my understanding grows a bit
more.

Thanks for chiming in.
Ed

 

[Walt]

Hi Ed,

The following thread also deals with this subject, is shorter, and
possibly will give you something you feel you can use:

http://groups.google.com/group/micr..._frm/thread/7d4e288b97478765/5f860f54a2f8e7fe

Walt Weber

 

[Ed]

Thanks, Walt. I'm still not sure about getting into this - it does look
quite a bit deeper than I imagined. Since I'm dealing with a smaller
specific situation, I can probably cobble something together to suit my
needs that wouldn't be a universal fit. In the meantime, as this could very
well be something I *will* need on a more universal basis, I've now got
several things to work my way through.

Cheers!
Ed

 

[Ed]

With excellent help from Bernie Deitrick in response to my post "Can I do
this with arrays?" (http://tinyurl.com/d2trc), I have found a method that
works for me.

I am trying to create a range that is all the cells in rng1 except the cells
in rng2. I iterated through all the cells of rng1, created a value from the
Row & Column of the cell, and read it into aryRng1. Repeated with rng2 and
aryRng2. Then, using Bernie's code, I removed all the values in ayrRng2
from aryRng1. Then I iterated through the revised aryRng1 and set a range
to .Cells(Left(aryRng1(x), Right(aryRng1(x)), using Union to add to the
range.

I don't know if this will stand up to all tests, or how fast it will be on
very large ranges (I'm working with less than 100 cells), but it does work
for me. I've posted the code for your reading pleasure.

Thank you, Walt, Tom, Norman and Bernie!
Ed

'*******************
Option Base 1

Sub TestSetRangeExcludingCells()

Dim aryRng1 As Variant 'cells of large range
Dim aryRng2 As Variant 'cells to be excluded

Dim rng1 As Range 'main range
Dim rng2 As Range 'range to be excluded
Dim rng3 As Range 'new range formed
Dim rngCl As Range 'temp range for cells

Dim x As Long, y As Long, z As Long
Dim a As Long, b As Long, c As Long

Dim wkb As Workbook
Dim wks As Worksheet

Set wkb = ActiveWorkbook
Set wks = wkb.Sheets("Sheet1")

Set rng1 = wks.Range("A1:E5")
Set rng2 = wks.Range("B1:B5")

x = rng1.Cells.Count
y = rng2.Cells.Count

' Read cell references from rng1 into array
ReDim aryRng1(1 To x) As Variant
c = 0
For Each rngCl In rng1
c = c + 1
aryRng1(c) = rngCl.Row & rngCl.Column
Next rngCl

' Read cell references from rng2 into array
ReDim aryRng2(1 To y) As Variant
c = 0
For Each rngCl In rng2
c = c + 1
aryRng2(c) = rngCl.Row & rngCl.Column
Next rngCl

' The following code is from Bernie Deitrick
' from the microsoft.public.excel.programming NG.
' It removes any value in aryRng2 from aryRng1.
' The result is all cell position references in rng1
' except those which also reference cells in rng2.
On Error Resume Next
For x = 1 To UBound(aryRng2)
aryRng1(Application.Match(aryRng2(x), aryRng1, False)) = ""
Next x

z = UBound(aryRng1)

For x = UBound(aryRng1) To 1 Step -1
If aryRng1(x) = "" Then
z = z - 1
For y = x To UBound(aryRng1) - 1
aryRng1(y) = aryRng1(y + 1)
Next y
End If
Next x

ReDim Preserve aryRng1(1 To z)
' Thank you, Bernie!

' Now set a range to the values of aryRng1
a = Left(aryRng1(1), 1)
b = Right(aryRng1(1), 1)
Set rng3 = wks.Cells(a, b)

For x = 1 To UBound(aryRng1)
a = Left(aryRng1(x), 1)
b = Right(aryRng1(x), 1)
Set rng3 = Union(rng3, wks.Cells(a, b))
Next x

rng3.Select

End Sub