VBA compare named ranges to see if they are identical

[jim101]

I have 2 named ranges Test1 and Test2,
Test1 is A1:I9 and Test2 is K11:S19,
The ranges will have number and blank cells in them, how can I compare them in VBA to let me know if they are identical.
Thanks

Some thing like this, but this does not work, for excel 2003

If Test1 = Test2 Then
MsgBox "Named Ranges Are The Same"
End If
End Sub

 

[MickG]

This is another possible Option:-
In the previous code, if the Data in each range was the same but the second range had another row added, then the code would Return both ranges as being the same, when that would not actually be true.

[COLOR="Navy"]Sub[/COLOR] MG20Aug52
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]If[/COLOR] Not Range("Test2").Rows.count = Range("Test3").Rows.count _
    Or Not Range("Test2").Columns.count = Range("Test3").Columns.count [COLOR="Navy"]Then[/COLOR]
        MsgBox "Disimilar sized Ranges !!": [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]For[/COLOR] Rw = 1 To Range("Test2").Rows.count
    [COLOR="Navy"]For[/COLOR] Col = 1 To Range("Test2").Columns.count
        [COLOR="Navy"]If[/COLOR] Not Range("Test2")(Rw, Col) = Range("Test3")(Rw, Col) [COLOR="Navy"]Then[/COLOR] _
            MsgBox "Disimilar range Values": [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
    [COLOR="Navy"]Next[/COLOR] Col
[COLOR="Navy"]Next[/COLOR] Rw
MsgBox "Both Ranges have the same data"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jim101]

Mick, Thanks for the explanation and the fix

 

[MickG]

Your welcome
Regards Mick