operations on ranges

[rene.lenaers]

Hi,

I have 2 ranges ; all the cells of the second one are included into the
first one :

set A = range("A15")
set B = range ("B2:C3")

Is there any VBA for Excel2000 instuction (or set of instructions) that
gives me the cells of A which are not in B (A - B) ?
It is not Union, nor Intersection, and "minus" is not allowed between ranges
....

Any help would be appreciated.!

Thanks

 

[David Hager]

Try these procedures from Dave Peterson.

<<I think you're going to have to loop through all the cells:

Option Explicit
Sub testme3()

Dim myRange As Range
Dim myExRange As Range
Dim myNewRange As Range
Dim myCell As Range

Set myRange = Range("a1:k350")
Set myExRange = Range("b2:j450")

For Each myCell In myRange
If Intersect(myCell, myExRange) Is Nothing Then
If myNewRange Is Nothing Then
Set myNewRange = myCell
Else
Set myNewRange = Union(myCell, myNewRange)
End If
End If
Next myCell

myNewRange.Select
MsgBox myNewRange.Address

End Sub

Actually, this worked a little quicker in my test case:


Sub testme4()

Dim tmpWks As Worksheet
Dim myNewRange As Range
Dim tmpRange As Range
Dim myRange As Range
Dim myExRange As Range

With ActiveSheet
Set myRange = .Range("a1:k350")
Set myExRange = .Range("b2:j450")

Set tmpWks = Worksheets.Add
With tmpWks
.Range(myRange.Address).Value = CVErr(xlErrNA)
.Range(myExRange.Address).ClearContents
Set tmpRange = .Cells.SpecialCells(xlCellTypeConstants,
xlErrors)
End With
Set myNewRange = .Range(tmpRange.Address)
Application.DisplayAlerts = False
tmpWks.Delete
Application.DisplayAlerts = True
End With

myNewRange.Select
MsgBox myNewRange.Address

End Sub

The second sub creates a new worksheet, fills in some data (errors), the
clears
the excluded range and uses the address of what's left (errors). Then
cleans
deletes the temporary worksheet.>>

 

[Alan Beban]

What is it you want to do with the data in A-B?

Alan Beban