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.