Delete row that contains keyword
 

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?

Delete row that contains keyword
 

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



Scott Wagner wrote:

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?

--

Dave Peterson

Delete row that contains keyword
 

Thank you sir, that worked perfectly!



"Dave Peterson" wrote:

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



Scott Wagner wrote:

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?

--

Dave Peterson