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!
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 ..
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
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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
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.
BRApplication.ScreenUpdating = False
Tommy
Originally Posted by tommy bak
And at the end of the macro...
At least for Office 2002 and up, as it doesn't reset itself.Application.ScreenUpdating = True
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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