macro to delete on a condition
 

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

macro to delete on a condition
 

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



Todd wrote:
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

macro to delete on a condition
 

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

"Todd" wrote:

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

macro to delete on a condition
 

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

--
HTH...

Jim Thomlinson


"Todd" wrote:

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