Multiple remove in VBA

[emlou187]

Hi,

I am trying to run something that will remove the entire row if a series of letters are present.

I am using the below code. It works, but I need to add in around another 25 letter combinations (i.e at the moment it removes EZY, but I also need to remove EX, EFT etc etc.)

Can anyone help??

Sub DeleteRows()
Dim c As Range
Dim SrchRng


Set SrchRng = ActiveSheet.Range("B1", ActiveSheet.Range("B65536").End(xlUp))
Do
Set c = SrchRng.Find("EZY", LookIn:=xlValues)

If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub

 

[Fluff]

Re: Struggling - multiple remove in VBA

If you are looking for exact matches try

Sub FilterDel()
   Dim Ary As Variant
   
   Ary = Array("EZY", "EX", "EFT")
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("H:H").AutoFilter 1, Ary, xlFilterValues
      .UsedRange.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[emlou187]

Re: Struggling - multiple remove in VBA

It is not exact, it just contains it. So there will be EZY123, EZY452, EZY634 etc etc and I need all those gone.

 

[Fluff]

Re: Struggling - multiple remove in VBA

In that case, try

Sub FilterDel()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("*EZY*", "*EX*", "*EFT*")
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      For i = LBound(Ary) To UBound(Ary)
         .Range("B:B").AutoFilter 1, Ary(i)
         .UsedRange.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      Next i
      .AutoFilterMode = False
   End With
End Sub

 

[emlou187]

Re: Struggling - multiple remove in VBA

No the data still isn't going anywhere ;-(

It's doing my head in, can't figure it out at all.

 

[Fluff]

Re: Struggling - multiple remove in VBA

If you step through the code using F8, does the data set get filtered? & are there any visible rows?

 

[emlou187]

Re: Struggling - multiple remove in VBA

It works if I keep it to 5 different codes to remove or less. The second I put more letter combinations in it just stops working. I have 32 different combos..........

 

[Fluff]

Re: Struggling - multiple remove in VBA

It should work regardless of how many values you have in the array.
How have you added them?

 

[emlou187]

Re: Struggling - multiple remove in VBA

It should work regardless of how many values you have in the array.
How have you added them?

Adding them manually, typing them in. Doesn't matter which order I put them in the first 5 will work and then not after that.

 

[vcoolio]

Re: Struggling - multiple remove in VBA

Hello Emlou187,

Here's another way that may work.

Place the 32 combinations in Column A of, say, Sheet2 starting in cell A1. Now apply the following code:-

Sub DeleteIt()

Dim ar As Variant
ar = Sheet2.Range("A1", Sheet2.Range("A" & Sheet2.Rows.Count).End(xlUp))

For i = 1 To UBound(ar)
    With Sheet1.[A1].CurrentRegion
        .AutoFilter 2, ar(i, 1), , , 7
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
Next i

End Sub

As you can see, I'm referencing the sheets by their sheet codes (Sheet1 and Sheet2). You could change Sheet1 to ActiveSheet in the above code.

I hope that this helps.

Cheerio,
vcoolio.