Msg Box for Numbers of Rows Comparison in the Same Worksheet

[nelsontan]

Hi All, I am new to VBA and also this forum, thanks for the help in advance

My intention is to have msgbox popup when
i. "Last Rows in Column C is LESS than Column A" = Additional Content Detected
ii. "Last Rows in Column C is MORE than Column A" = Redundant Row To Delete

I would like to confirm is the 2 or more msgbox can popup together? As I have another msgbox to check the error in the worksheet (e.g. #N/A)

Example as below

Column A	Column B	Column C (Formulas - vlookup)
AA	11	'=VLOOKUP(A2,'Sheet2'!A:C,2,FALSE)
BB	22	'=VLOOKUP(A3,'Sheet2'!A:C,2,FALSE)
CC	33

My codes (Not Working btw)

Sub Msgbox()

Dim Alert2A, Alert2B as range
    'To check additional rows
    On Error Resume Next
    Set Alert2A = Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    Set Alert2B = Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row
    On Error GoTo 0
    If Alert2A < Alert2B Then
        MsgBox "Redundant Rows To Delete"
    Else
        MsgBox "Additional Rows Detected"
        Exit Sub
    End If

End Sub

 

[Marc L]

Hi,​

​

it's what happens when using useless On Error Resume Next codeline which masks where your logic fails !​

As obviously a row # can't be a range object …​

Also you forgot a condition within your If … End If block …​

​

And you can avoid useless variables just comparing directly the rows indexes :​

Sub Demo1()
    Select Case Cells(Rows.Count, 1).End(xlUp).Row - Cells(Rows.Count, 3).End(xlUp).Row
           Case Is < 0: MsgBox "Redundant Rows To Delete"
           Case Is > 0: MsgBox "Additional Rows Detected"
    End Select
End Sub

 

[nelsontan]

Hi,​

​

it's what happens when using useless On Error Resume Next codeline which masks where your logic fails !​

As obviously a row # can't be a range object …​

Also you forgot a condition within your If … End If block …​

​

And you can avoid useless variables just comparing directly the rows indexes :​

Sub Demo1()
    Select Case Cells(Rows.Count, 1).End(xlUp).Row - Cells(Rows.Count, 3).End(xlUp).Row
           Case Is < 0: MsgBox "Redundant Rows To Delete"
           Case Is > 0: MsgBox "Additional Rows Detected"
    End Select
End Sub

Thanks for the useful info , but how can I show 2 msgbox together as I have inserted one msgbox before to check the errors in the worksheet. I've tried to run the code but it always show 1 msgbox only.

   'To check error
    On Error Resume Next
    Set Alert = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    On Error GoTo 0
    
    'Alert Message if error found
    If Alert Is Nothing Then
    Else
        MsgBox "Error Detected (e.g. #N/A)"
        Exit Sub
    End If
    
    'To check additional rows
    Select Case Cells(Rows.Count, 1).End(xlUp).Row - Cells(Rows.Count, 9).End(xlUp).Row
           Case Is < 0: MsgBox "Redundant Rows To Delete"
           Case Is > 0: MsgBox "Additional Rows Detected"
    End Select

 

[nelsontan]

Hi, can any expert help on this? Or I need to create a new thread for this due to different topic?

 

[Marc L]

​

If you want a single message box just concatenate the message within a String variable …​

 

[nelsontan]

​

If you want a single message box just concatenate the message within a String variable …​

You mean to combine two different scenarios (error checking and to detect additional content) into one message box? How to do it?

 

[Marc L]

but how can I show 2 msgbox together

What that really means ?​

 

[nelsontan]

What that really means ?​

I planned to show 2 msgbox for following purposes:
1. Error checking
2. To detect additional content

But at the end it only show the 1st one, after i click okay in the msgbox then that's the end, i couldn't see my 2nd msgbox.

 

[Marc L]

​

Obviously normal according to your Exit Sub codeline …​