macro to delete on a condition

[Guest]

Hi,

I am trying to delete entire rows based on a condition. My condition is
simple, I put the word "delete" in column b of the worksheet. If it has
"delete" then wa lah! Its supposed to be gone. Except I can't figure out
how to get it done. I've tried a different criteria with putting in a number
instead but I am useless here. Please help?

Todd

Sub deleteif()
On Error Resume Next
For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
If UCase(Cells(i, "c")) = "delete" Then
EntireRow.Delete
End If
Next i
End Sub

 

[Mark Driscol]

UCase(Cells(i, "c")), which capitalizes every letter, will never equal
"delete", which has all lower-case letters. Try UCase(Cells(i, "c")) =
"DELETE".

Also, "EntireRow.Delete" be something like "Rows(i).EntireRow.Delete".


Mark

 

[Guest]

This code will work assuming you have continuous data in column A

Sub Delete_Row()

Dim x
x = 1

Do While Cells(x, 1).Value <> ""

If Cells(x, 2).Value = "delete" Or Cells(x, 2).Value = "Delete" Then
Rows(x).delete
Else
x = x + 1
End If

Loop

End Sub

 

[Guest]

You can give this a try. It should be faster than your existing code...

Sub DeleteRows()
Dim wks As Worksheet
Dim rngFound As Range
Dim rngToSearch As Range
Dim strFirst As String
Dim rngToDelete As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Columns("C")
Set rngFound = rngToSearch.Find(What:="Delete", _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Nothing to Delete"
Else
strFirst= rngFound.Address
Set rngToDelete = rngFound
Do
Set rngToDelete = Union(rngToDelete, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirst
rngToDelete.EntireRow.Delete
End If

End Sub