Delete row that contains keyword

S

Scott Wagner

Looking to clean up a list of items by deleting certain unneeded data. The
lines I want to get rid of contain certain keywords. How (with VBA) can I
delete those rows?

Example: (have now)
LN QTY Description
8 1 Panelboard, Type AE (101)
13 20A 1 Pole TEY
17 20A 1 Pole TEY Space
6 20A 2 Pole TEY
1 Copper Bus Heat Rated
1 Nameplates
1 Ground main lug TGL20
4 Ground-Box bonded TGL2
1 AB43B Box
1 AF43SN Front
1 AEF3422MBX Interior AXB7

Example: (want)
LN QTY Description
8 1 Panelboard, Type AE (101)
1 AB43B Box
1 AF43SN Front

(keywords were "interior, ground, nameplate, pole, copper")

Any ideas?
 
D

Dave Peterson

I don't see how that list got rid of all the rows, but...

Option Explicit
Sub CleanPlusses()
Dim FoundCell As Range
Dim myWords As Variant
Dim wks As Worksheet
Dim iCtr As Long

myWords = Array("interior", "ground", "nameplate", "pole", "copper")

Set wks = ActiveSheet
With wks
With .Range("C:C")
For iCtr = LBound(myWords) To UBound(myWords)
Do
Set FoundCell = .Cells.Find(what:=myWords(iCtr), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
Next iCtr
End With
End With

End Sub
 
S

Scott Wagner

Thank you sir, that worked perfectly!



Dave Peterson said:
I don't see how that list got rid of all the rows, but...

Option Explicit
Sub CleanPlusses()
Dim FoundCell As Range
Dim myWords As Variant
Dim wks As Worksheet
Dim iCtr As Long

myWords = Array("interior", "ground", "nameplate", "pole", "copper")

Set wks = ActiveSheet
With wks
With .Range("C:C")
For iCtr = LBound(myWords) To UBound(myWords)
Do
Set FoundCell = .Cells.Find(what:=myWords(iCtr), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
Next iCtr
End With
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top