Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Dear Experts,

Could you please have a look at the attached file, it contains Sheet1 and Sheet2 with certain header texts in the first row.

I know that the Excel easily can compare cell values whether those are equal like whether A1=A2, but is it possible in VBA to do this for ranges whether two ranges are the same?

At the certain example the question would be whether A1:F1 on Sheet1 is equal to A1:F1, meaning that the cells exactly are in the same order and containing the same text?

thanks,

HeaderCheck.xls

Could you please have a look at the attached file, it contains Sheet1 and Sheet2 with certain header texts in the first row.

I know that the Excel easily can compare cell values whether those are equal like whether A1=A2, but is it possible in VBA to do this for ranges whether two ranges are the same?

At the certain example the question would be whether A1:F1 on Sheet1 is equal to A1:F1, meaning that the cells exactly are in the same order and containing the same text?

thanks,

HeaderCheck.xls

```
Sub Sample()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim boolMatch As Boolean
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
boolMatch = True
Do Until ws1Value = ""
Do Until ws2Value = ""
If ws2Value <> ws1Value Then
boolMatch = False
Exit Do
End If
X = X + 1
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
Loop
X = 0
Y = Y + 1
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
Loop
If boolMatch = True Then MsgBox "Range is Identical" Else _
MsgBox "Range is not Identical"
End Sub
```

Sid

Sid

```
Option Explicit
Sub Sample()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim boolMatch As Boolean
Dim X As Long, Y As Long
Dim ws1Value, ws2Value
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
boolMatch = True
Do Until ws1Value = ""
Do Until ws2Value = ""
If ws2Value <> ws1Value Then
boolMatch = False
Exit Do
End If
X = X + 1
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
Loop
X = 0
Y = Y + 1
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
Loop
If boolMatch = True Then MsgBox "Range is Identical" Else _
MsgBox "Range is not Identical"
End Sub
```

```
Option Explicit
Sub Sample()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim boolMatch As Boolean
Dim X As Long, Y As Long
Dim ws1Value, ws2Value
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
boolMatch = True
Do Until ws2Value = ""
If ws2Value <> ws1Value Then
boolMatch = False
Exit Do
End If
Y = Y + 1
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
Loop
If boolMatch = True Then MsgBox "Range is Identical" Else _
MsgBox "Range is not Identical"
End Sub
```

Sid
```
Option Explicit
Sub Sample()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim boolMatch As Boolean
Dim X As Long, Y As Long
Dim ws1Value, ws2Value
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
If ws1Value = "" Or ws2Value = "" Then boolMatch = False Else _
boolMatch = True
Do Until ws2Value = ""
If ws2Value <> ws1Value Then
boolMatch = False
Exit Do
End If
Y = Y + 1
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
Loop
If boolMatch = True Then MsgBox "Range is Identical" Else _
MsgBox "Range is not Identical"
End Sub
```

Sid

```
Sub RangeCompare()
Dim Range1 As Range
Dim Range2 As Range
Set Range1 = Worksheets("Sheet1").Range("A1:F1")
Set Range2 = Worksheets("Sheet2").Range("A1:F1")
If "Range1" = "Range2" Then MsgBox "Ranges are identical" Else MsgBox "Ranges are not identical"
End Sub
```

Quick question. You want this code to simply check the headers in a row. right? And you do not want to specify a fix range (except the starting cell)?

Sid

Yes the target would be that the cells exactly are in the same order and containing the same text.

Maybe as in the logic in the below not working code, so defining two ranges and compare

Sub RangeCompare()

Dim Range1 As Range

Dim Range2 As Range

Set Range1 = Worksheets("Sheet1").Range

Set Range2 = Worksheets("Sheet2").Range

If "Range1" = "Range2" Then MsgBox "Ranges are identical" Else MsgBox "Ranges are not identical"

End Sub

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Open in new window

Sid