Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
get number of rows
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
get number of rows
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(xlCellTy peLastCell).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 "Rachel" skrev i meddelelsen ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
get number of rows
Since you didn't give anything about your data structure I wrote this macro that will handle any case. I don't know which columns contain data. this macro only looks at the last row of data and not for blanks in the middle of the data. You request for number of USED ROWS which 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 Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161620 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
get number of rows
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 -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Rachel" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a number of rows based on the number of columns filled bytext values | Excel Programming | |||
Fill Cells with same number in three rows then skip to next number | Excel Worksheet Functions | |||
Get number of rows that data uses, including blank rows | Excel Discussion (Misc queries) | |||
Get number of rows that data takes up, including blank rows | Excel Worksheet Functions | |||
flexible paste rows function that inserts the right number of rows | Excel Discussion (Misc queries) |