Selecting a variable string from a list in VBA code

[matratus34]

Hi all,

I get data sets that I need to clean up by removing lines in the data that contain certain words.
I've manged to get some code to work that looks for a word in column D by filtering on that column and then deleting out those lines.

In my code the search word is set.

What I want to do is have a list of search words in column A on "Sheet2" and have the code loop through the list without me manually changing the word.

I've tried various loops but as usual keep failing!

Any help is greatly appreciated as always.

My current code is below.




Sub Test_Filter()


Dim ws As Worksheet
Dim DataRow As Long
Dim strSearch As String



'Select Active Worksheet
Set ws = ThisWorkbook.Worksheets("Data")


'Search for Word
strSearch = "Dog"


With ws
'~~> Remove any filters
.AutoFilterMode = False


DataRow = .Range("D" & .Rows.Count).End(xlUp).Row


With .Range("D1:D" & DataRow)
.AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With


'~~> Remove any filters
.AutoFilterMode = False
End With
End Sub

 

[matratus34]

To try and make my request a bit simpler to understand - on my code above the search variable is set to "Dog" - and so would need to be manually changed to run again for a different word.

What I've been trying to do is set a loop so that the variable changes to a list in column A on Sheet2 each time it runs.

So for example, on Sheet2 the list in column A might be:

Dog
Cat
Wolf
Rabbit
Lion

and each time the macro loops it takes the next word in the list.


Thanks again

 

[matratus34]

I've now worked out how to get my strSearch variable to look at my list of words and loop through them:

Sub LoopTheList()


Dim strSearch As String
Dim i As Long


Dim DataSheet As Worksheet
Dim OOS As Worksheet
     
Set DataSheet = Sheets("Data")
    Set OOS = Sheets("Sheet2")
    
i = 1


Do


strSearch = OOS.Cells(i, 1)


i = i + 1


Loop Until OOS.Cells(i, 1) = ""


End Sub

I now just need to know who to link my 2 macros together?!
:D

 

[Fluff]

How about

Sub LoopTheList()
   Dim Cl As Range
   Dim DataSheet As Worksheet
   Dim OOS As Worksheet
        
   Set DataSheet = Sheets("Data")
   Set OOS = Sheets("Sheet2")
   
   With DataSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      For Each Cl In OOS.Range("A1", OOS.Range("A" & Rows.Count).End(xlUp))
         .Range("D:D").AutoFilter 1, "*" & Cl.Value & "*"
         .AutoFilter.Range.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      Next Cl
      .AutoFilterMode = False
   End With
End Sub

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub LoopTheList()
   Dim Cell As Range, ListSheet As Worksheet, DataSheet As Worksheet
   Set DataSheet = Sheets("Data")
   Set ListSheet = Sheets("Sheet2")
   For Each Cell In ListSheet.Range("A1", ListSheet.Range("A" & Rows.Count).End(xlUp))
     DataSheet.Columns("D").Replace "*" & Cell.Value & "*", "#N/A", xlWhole, , False, , False, False
   Next
   On Error GoTo NoneFound
   DataSheet.Columns.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoneFound:
End Sub[/td]
[/tr]
[/table]

 

[matratus34]

Thanks for the help guys - appropriated as always !!

 

[Fluff]

You're welcome