dim rng1 as range
dim rng2 as range
set rng1 = worksheets("sheet1").usedrange
set rng2 = worksheets("sheet2").usedrange
if intersect(rng1, rng1.parent.range(rng2.address)) is nothing then
'no match.
.....
rng1.parent refers to the worksheet that holds rng1. I think it makes it easier
to copy code to other projects if you do it that way.
but you could:
if intersect(rng1, worksheets("sheet1").range(rng2.address)) is nothing then
.....
========
Here's one way:
Option Explicit
Sub testme01()
Dim rng1 As Range
Dim rng2 As Range
Dim myIntersect As Range
Dim myCell As Range
Dim myOutside As Range
Set rng1 = Worksheets("sheet1").UsedRange
Set rng2 = Worksheets("sheet2").UsedRange
If rng1.Address = rng2.Address Then
MsgBox "same range"
Exit Sub
End If
If Union(rng1, rng1.Parent.Range(rng2.Address)).Address = rng1.Address Then
MsgBox "all within Rng1 address"
Exit Sub
End If
Set myIntersect = Intersect(rng1, rng1.Parent.Range(rng2.Address))
If myIntersect Is Nothing Then
MsgBox "no intersection"
Else
For Each myCell In rng2.Cells
If Intersect(myIntersect, _
rng1.Parent.Range(myCell.Address)) Is Nothing Then
If myOutside Is Nothing Then
Set myOutside = myCell
Else
Set myOutside = Union(myCell, myOutside)
End If
End If
Next myCell
End If
If myOutside Is Nothing Then
'do nothing
Else
MsgBox "Outside: " & myOutside.Address & vbLf _
& "Rng1: " & rng1.Address & vbLf _
& "Rng2: " & rng2.Address
End If
'and just because:
If Union(rng1, rng1.Parent.Range(rng2.Address)).Address = rng1.Address Then
MsgBox "all within Rng1 address"
End If
If Union(rng1, rng1.Parent.Range(rng2.Address)).Address = rng2.Address Then
MsgBox "all within Rng2 address"
End If
End Sub
Wild Bill wrote:
I'm attempting to compare .UsedRange from two sheets and get the
addresses of Sheet2.UsedRange that are not addresses in
Sheet1.UsedRange. I wish I could test the Intersect() but they're
separate sheets.
My first thought was to For Each the cells in Sheet2, but I couldn't
come up with how to see if they were .Used in Sheet1. Maybe parse the
address, giving e.g. "E3" and use Intersect on Sheet1!E3 and
sheet1.usedrange?
My next demented thought was to create a temp sheet and create a Range1
there with the exact dimensions and starting point of Sheet1's
.UsedRange, and Range2 similarly, then perhaps go
for each rng in Range2
if not intersect(rng,Range1)
'a "non-match"
end if
next rng
This seems wasteful, but I'm just thinking out loud.
Please tell me that something like the first approach can work!
--
Dave Peterson
