Delete row based on 3 conditions

[Nicolaf]

Hi,

I have a macro that deletes a row if two cells have different value (cells in column A and G).

I would now like to add two more conditions in order to proceed with deleting row:

1. the cell in column G is numeric (as opposed to text).
2. the cell in column B does not contain word "Final"

Dim wFinalResult As Worksheet
 
ActiveWorkbook.Sheets("FinalResult").Activate
 
Set wFinalResult = ActiveSheet
 
Dim N As Long, i As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    For i = N To 2 Step -1
        If Cells(i, "A").Value <> Cells(i, "G").Value Then
            Cells(i, "A").EntireRow.Delete
        End If
    Next i
End Sub

How would I do that?

Nic

[mperrah]

try this

Sub delRowIfCond3()
Dim wFinalResult As Worksheet
Dim N As Long, i As Long

ActiveWorkbook.Sheets("FinalResult").Activate
 
Set wFinalResult = ActiveSheet

N = Cells(Rows.Count, "A").End(xlUp).Row
    For i = N To 2 Step -1
        If Cells(i, "A").Value <> Cells(i, "G").Value And _
           IsNumeric(Cells(i, "G").Value) And _
           Cells(i, "B").Value <> "Final" Then ' note this is case sensitive
           Cells(i, "A").EntireRow.Delete
        End If
    Next i
End Sub

be aware the "Final" to search in Column B is case sensitive.
I made sample code to test and if final is lower case it will delete that row too.

If that is a possibility you can add another If portion:

Cells(i, "B").Value <> "final" Then ' or And _

cheers
-mark

[Nicolaf]

Thanks!

[JKwan]

you can use

Option Compare Text

This will make "AAA" = "aaa"
This will make it global

If you want something more local, I usually do this

 If LCase(xx) = "whatever" Then ....

or

 If UCase(xx) = "WHATEVER" Then ....