Intersecting addresses from two sheets

  • Thread starter Thread starter Wild Bill
  • Start date Start date
W

Wild Bill

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!
 
One way right off the top of my head:
Get the cell addresses of the starting and ending cells of the Sheet1
UsedRange.
Use the Intersect on Sheet2 comparing the address of the Sheet2 UsedRange
with the address you got from Sheet1 UsedRange. The Intersect will never
know that you are talking about two separate sheets. HTH Otto
 
I am a newbie and I don't know the answer, but if you don't mind I
would like to add to the question. I have XP version.

I have tried this line
If Intersect(Sht.Range("$A$1"), Sht.UsedRange) Then
which fails at runtime - Type Mismatch.

But in the debug window
?Intersect(Sht.Range("$A$1"), Sht.UsedRange)
shows the contents of cell A1!

(Not only that...
Intersect(Sht.Cells(1, 1), Sht.UsedRange)
doesn't work in either! I thought both my args are ranges??)

So (pushing my luck) 2 questions:
1. I thought Intersect returned a range. Is the debug window just
showing the .Value as a convenience?
2. Why does the statement work in the debug window but fail in code?
(and why does the .Cells fail)

NOTE: Sht is not the active sheet. I figured that might be important!
 
You're treating that Intesect() expression as a boolean (= true or = false).

Try

if intersect(...) is nothing then
'nothing in common
else
'something in common
end if

1. Intersect will return a range or Nothing (depending)
2. And the watch window will show you the default property's value (.value for
range objects) as a help.
 
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
 
Back
Top