Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Macro with OR/ELSE condition | Excel Discussion (Misc queries) | |||
Delete Row with condition | Excel Discussion (Misc queries) | |||
Macro to delete rows based on a condition | Excel Worksheet Functions | |||
Can I delete an entire row if condition is not met? | Excel Worksheet Functions | |||
delete rows with certain condition | Excel Programming |