get number of rows

[Rachel]

HI,

Anyone who can help?

I have 2 worksheets. I want to compare the number of used rows of both
worksheet, If the number does not match, I want it to display a ERROR MSG
"Sheet1 has (blank) number and Sheet2 has (blank) number. Do you wish to
proceed?"

Any help will be highly appreciated. THANKS!

 

[Per Jessen]

Hi

The macro below uses two approaches to calculate number of rows. r1
calculate last row in used range. r2 calculate last row with data in column
A.

Sub aaa()
r1 = Sheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Row
r2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
If r1 <> r2 Then
msg = MsgBox("Sheet1 has " & r1 & " number and Sheet 2 has " & r2 & "
number." _
& vbLf & vbLf & "Do you wish to proceed?", vbExclamation + vbYesNo,
"Error")
If msg = vbNo Then Exit Sub
End If
End Sub

Regards,
Per

 

[ryguy7272]

Here is some code to help you do comparisons:

#1)
Sub Compare2Shts()
For Each Cell In Worksheets("CompareSheet#1").UsedRange
If Cell.Value <> Worksheets("CompareSheet#2").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next

For Each Cell In Worksheets("CompareSheet#2").UsedRange
If Cell.Value <> Worksheets("CompareSheet#1").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next
End Sub


Sub CompareAnother2Shts()
For Each Cell In Worksheets("CompareSheet#1").Range("A1:J50")
If Cell.Value <> Worksheets("CompareSheet#2").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next

For Each Cell In Worksheets("CompareSheet#2").Range("A1:J50")
If Cell.Value <> Worksheets("CompareSheet#1").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next
End Sub

#2)
Sub FindDupes() 'assuming both sheets are in same book and book is open
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim str As String
str = InputBox("Type name of first sheet")
Set sht1 = Worksheets(str)
str = InputBox("Type name of second sheet")
Set sht2 = Worksheets(str)


sht1.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht1 = ActiveCell.Row

sht2.Activate
sht2.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht2 = ActiveCell.Row

sht1.Activate
For rowSht1 = 1 To LastRowSht1
If sht1.Cells(rowSht1, 1) = "" Then Exit Sub
For rowSht2 = 1 To LastRowSht2
If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value
Then
sht1.Cells(rowSht1, 1).Interior.ColorIndex = 3
sht2.Cells(rowSht2, 1).Interior.ColorIndex = 3

End If
Next
Next
sht1.Cells(1, 1).Select
End Sub


#3)
Sub checkrev()

With Sheets("Sheet1")
Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = .Range("A1:A" & Sh1LastRow)
End With
With Sheets("Sheet2")
Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh2Range = .Range("A1:A" & Sh2LastRow)
End With

'compare sheet 1 with sheet 2
For Each Sh1cell In Sh1Range
Set c = Sh2Range.Find( _
what:=Sh1cell, LookIn:=xlValues)
If c Is Nothing Then
Sh1cell.Interior.ColorIndex = 3
Sh1cell.Offset(0, 1).Interior.ColorIndex = 3
Else
If Sh1cell.Offset(0, 1) <> c.Offset(0, 1) Then
Sh1cell.Interior.ColorIndex = 6
Sh1cell.Offset(0, 1).Interior.ColorIndex = 6
End If
End If
Next Sh1cell
'compare sheet 2 with sheet 1
For Each Sh2cell In Sh2Range
Set c = Sh1Range.Find( _
what:=Sh2cell, LookIn:=xlValues)
If c Is Nothing Then
Sh2cell.Interior.ColorIndex = 3
Sh2cell.Offset(0, 1).Interior.ColorIndex = 3
Else
If Sh2cell.Offset(0, 1) <> c.Offset(0, 1) Then
Sh2cell.Interior.ColorIndex = 6
Sh2cell.Offset(0, 1).Interior.ColorIndex = 6
End If
End If
Next Sh2cell

End Sub



HTH,
Ryan