Delete certain rows

I need to search through an entire column. If the values E, A, F, or P are found in that column I need to delete the entire row.

On another sheet I need to delete the rows if they DO NOT have those values. Any help is appreciated.

Thanks!

Hi,

Would something like this do you ..

Code:

---

Option Explicit
Sub forHondaTiger()
    Dim lastRow As Long, i As Long, cel As Range
    lastRow = Range("A65536").End(xlUp).Row
    For i = lastRow To 1 Step -1
    If Range("A" & i).Value = "E" Or Range("A" & i).Value = "A" Or _
        Range("A" & i).Value = "F" Or Range("A" & i).Value = "P" Then
        Range("A" & i).EntireRow.Delete
    End If
    Next i
End Sub

---

Ok, I'm a bit to slow but here it is anyway
Place the cursor in the column you wish to search and play the appropiate makro

Code:

---

Sub DeleteAEFP()
Dim test As Boolean, x As Long, lastrow As Long, col As Long
col = ActiveCell.Column
lastrow = Cells(65536, col).End(xlUp).Row
For x = lastrow To 1 Step -1
    test = Cells(x, col).Text Like "[AEFPaefp]"
    If test = True Then Cells(x, col).EntireRow.Delete
Next
End Sub
 
 
Sub LeaveOnlyAEFP()
Dim test As Boolean, x As Long, lastrow As Long, col As Long
col = ActiveCell.Column
lastrow = Cells(65536, col).End(xlUp).Row
For x = lastrow To 1 Step -1
    test = Cells(x, col).Text Like "[AEFPaefp]"
    If test = False Then Cells(x, col).EntireRow.Delete
Next
End Sub

---

BR
Tommy Bak

Thanks Tommy that was exactly what I needed.

You're welcome, hondatiger.
If you want to speed it up a bit insert this line in the top of each macro.

Code:

---

Application.ScreenUpdating = False

---

BR
Tommy

Quote:

---

Originally Posted by tommy bak

You're welcome, hondatiger.
If you want to speed it up a bit insert this line in the top of each macro.

Code:

---

Application.ScreenUpdating = False

---

BR
Tommy

---

And at the end of the macro...

Code:

---

Application.ScreenUpdating = True

---

At least for Office 2002 and up, as it doesn't reset itself.

I have heard about this before, Zack, but I have never experienced it myself.
But you're right... one should do this as a precaution

br
Tommy Bak

Yeah, it doesn't make a huge difference, but when doing a lot of coding/using it does work better when you take that precaution. In my work add-in (that I pass around for everybody, common workplace functions) I have a procedure on mine that resets all application attributes. If I've been doing a lot of coding and/or testing, I usually run that routine to clean up after myself. It seems to help a little bit. I am messy though.. LOL :)

If you can avoid looping it will save some time on the bigger datasets. This method uses AutoFilter on an inserted column to delete the dersired rows

Cheers

Dave

Code:

---

Sub DelRowsII()
    Dim myRange As Range, myCol As String
    myCol = "A"
Set myRange = Range(Cells(1, myCol), Cells(65536, myCol).End(xlUp))
Application.ScreenUpdating = False
    'insert a calculation row
    myRange.Offset(0, 1).Columns.Insert
With myRange.Offset(0, 1)
    .Formula = "=IF(OR(RC[-1]={""A"",""E"",""F"",""P""}),1,"""")"
    .Value = .Value
    'delete the rows with a 1 as they contain the matches
    .AutoFilter Field:=1, Criteria1:="1"
    .EntireRow.Delete
    .EntireColumn.Delete
    End With
Application.ScreenUpdating = True
End Sub

---