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

 

[joel]

Since you didn't give anything about your data structure I wrote thi
macro that will handle any case. I don't know which columns contai
data. this macro only looks at the last row of data and not for blank
in the middle of the data. You request for number of USED ROWS whic
could mean a few different things.


Sub BlankRows()

Sht1LastRow = 0
Sht2LastRow = 0
For ColCount = 1 To Columns.Count
LastRow = Sheets("Sheet1").Cells(Rows.Count
ColCount).End(xlUp).Row
If LastRow > Sht1LastRow Then
Sht1LastRow = LastRow
End If

LastRow = Sheets("Sheet2").Cells(Rows.Count
ColCount).End(xlUp).Row
If LastRow > Sht2LastRow Then
Sht2LastRow = LastRow
End If

Next ColCount

If Sht1LastRow <> Sht2LastRow Then
If Sht1LastRow > Sht2LastRow Then
MsgBox ("Sheet2 has (blank) number")
Else
MsgBox ("Sheet1 has (blank) number")
End If

Response = MsgBox("Do you want to proceed", vbYesNo)
If Response = vbNo Then Exit Sub
End If

End Su

 

[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