PDA

View Full Version : Solved: Delete certain rows



HondaTiger
07-29-2004, 04:20 PM
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
07-29-2004, 04:42 PM
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
07-29-2004, 05:04 PM
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
07-30-2004, 07:41 AM
Thanks Tommy that was exactly what I needed.

tommy bak
07-30-2004, 12:43 PM
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
07-30-2004, 03:27 PM
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
07-31-2004, 04:22 AM
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
07-31-2004, 05:44 PM
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
08-01-2004, 06:45 AM
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