Delete rows where Text is

[Crocdundee]

Excel 2007

Hi can someone please help here.
I would like code to cycle down a column (D) and if text "TAB" is in a cel, then delete row.

Thank you in advance
Graham

 

[pedie]

try

[/FONT]
[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub try()
 
 Dim i As Range
 Dim lr As Long
 
 lr = Range("D" & Rows.Count).End(xlUp).Row
 For Each i In Range("D1:D" & lr)
  If i.Value = "TAB" Then
   i.EntireRow.Delete
  End If
 Next i
End Sub

 

[Crocdundee]

Thank you for this works fine in a macro
May I try my luck for another.
I have duplicate times in C:2 C:
land the data looks like this 1:00:00 PM
I modified your code but it dosen't work
----------------------------------
Dim i As Range
Dim lr As Long

lr = Range("C" & Rows.Count).End(xlUp).Row
For Each i In Range("C1:C" & lr)
If i.Value = "-.PM" Then
i.EntireRow.Delete
End If
Next i
End Sub
---------------------
As you can see I used a *.PM as a value.

Hope you may help
And thanks very much for the code
Graham

 

[pedie]

try something like this...

[/FONT]
[FONT=Courier New]If i.text like "*PM" Then[/FONT]
[FONT=Courier New]

 

[Crocdundee]

Thanks very much
Put it into a macro, and I have to run the hotkeys 4 times to get all the rows deleted..............Strange........But it works
Thanks very much
graham

 

[GTO]

Thanks very much
Put it into a macro, and I have to run the hotkeys 4 times to get all the rows deleted..............Strange........But it works
Thanks very much
graham

Hi there,

Borrowing a bit of Pedie's code, let's loop 'backwards', from bottom to top. This way, you do not end up missing rows.

Sub exa()
Dim i As Long
Dim lr As Long
    
' to adjust for any header rows
Const TOP_ROW = 2
    
    lr = Range("D" & Rows.Count).End(xlUp).Row
    For i = lr To TOP_ROW Step -1
        If UCase(Cells(i, "D").Value) = "TAB" Then
            Cells(i, "D").EntireRow.Delete
        End If
    Next
End Sub

To explain: If you use a For...Each, it naturally reads left-to-right and top-to-bottom. Since we're only checking one column, its that top-to-bottom part that is the problem. Let's say there are values that meet our test (ie "TAB") in rows 2, 3, 4. As the For...Each runs, it first checks row 2 and deletes it. Then it checks row 3, BUT... the 'old' row 3 is now in row 2, so it gets skipped. Does that make sense?

Hope that helps,

Mark