Delete certain rows

[HondaTiger]

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!

[Zack Barresse]

Hi,

Would something like this do you ..

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

[tommy bak]

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

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

[HondaTiger]

Thanks Tommy that was exactly what I needed.

[tommy bak]

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

Application.ScreenUpdating = False

BR
Tommy

[Zack Barresse]

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

Application.ScreenUpdating = False

BR
Tommy

And at the end of the macro...

Application.ScreenUpdating = True

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

[tommy bak]

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

[Zack Barresse]

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

[brettdj]

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

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