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