Need to Delete Multiple Row based on Conditions

[jdlee]

Hi, I am a noob to VBA. I would like to create a macro that will delete entire rows based on multiple values. I have a spread sheet with several columns. I want the Macro to look in Column AA and if the Value = "Target", then look in column C for the value "3", then delete the entire row. Any help would be greatly appreciated.

Joshua

[jdlee]

I would like to mention the name of the sheet I am using is called "CurrentF"

[ashu1990]

Try This Easy and works every time

Sub test()
Range("A1").Select
Do While ActiveCell.Address(0, 0) <> Range("A65536").End(xlUp).Offset(1, 0).Address(0, 0)
If LCase(ActiveCell.Value) = "target" And ActiveCell.Offset(0, 2).Value = 3 Then
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop


End Sub

[Excel Fox]

Sub DeleteRows()

    Dim lng As Long
    With Worksheets("CurrentF")
        lng = .Cells(Rows.Count, "AA").End(xlUp).Row
        For lng = lng To 1 Step -1
            If Trim(.Cells(lng, "AA").Value) & Trim(.Cells(lng, "C").Value) = "Target3" Then
                .Rows(lng).Delete
            End If
        Next lng
    End With
    
End Sub

[Rick Rothstein]

Another way (uses no loops)...

Sub DeleteThreesAndTargets()
  Dim LastRow As Long, UnusedColumn As Long
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
  With Range(Cells(1, UnusedColumn), Cells(LastRow, UnusedColumn))
    .Value = Evaluate("IF((C1:C" & LastRow & "=3)*(AA1:AA" & _
             LastRow & "=""target""),C1:C" & LastRow & ","""")")
    On Error Resume Next
    .SpecialCells(xlConstants).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub

This method does have a restriction of no more than 8192 individual ranges (areas) of rows (contiguous rows count as one area) meeting your conditions for deletion. That means you can have at least 16394 rows of data before the code might give you trouble.