Pick Another word

[Np72]

Hi,

I have this coding (which I've modified for my own purpose) and I wanted to add more words i.e. current Account (Vm) and Current account (VI). I've gone down the road of filtering then deleting but it just looks........messy ! Is there a way of adding these 2 current Account (Vm) and Current account (VI) into the coding.

Many Thanks

Sub FindAndRemove()
Dim i As Long
Dim j As Long
Dim pickWord As Variant
Dim pickAnother As Variant
pickWord = "margin"
pickAnother = "standard class"

For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
Set w = Range("B" & i).Find(pickWord, LookIn:=xlValues)
Set y = Range("B" & i).Find(pickAnother, LookIn:=xlValues)
If w Is Nothing And y Is Nothing Then
Rows(i).EntireRow.Delete
If Range("B" & i) = "" Then Exit Sub
i = i - 1
End If
Next i


End Sub

 

[wigi]

Hello

What Excel version do you use?

 

[wigi]

Anyway, try:

Sub wigi()

    Dim i As Long
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
    
    vWords = Array("margin", "standard class", "current Account (Vm)", "Current account (VI)")


    With Cells(1).CurrentRegion.Columns(2)
        sq = .Value
        For i = 2 To UBound(sq)
            If IsError(WorksheetFunction.Match(sq(i, 1), vWords, 0)) Then sq(i, 1) = ""
        Next
        .Value = sq
        .SpecialCells(4).EntireRow.Delete
    End With


End Sub

 

[Np72]

Hi.

Thanks for this and apologies for my delay in replying. I'm on 2007, I've used this coding and keep getting a sub or function not defined error, so am in the process of figuring this out

 

[Np72]

Hi,

Sussed it, but the macro is now deleting the whole sheet and not leaving Array("margin", "standard class", "current Account (Vm)", "Current account (VI)")

 

[Np72]

Nope sussed it again !

 

[Np72]

Hi,

Actually as a slight spin on this, what if I wanted to have this as like margin", "standard class", "current Account (Vm)", "Current account (VI)")

 

[wigi]

I don't understand what you are asking now. Sorry.

 

[Np72]

Instead of looking for those particular words can it look for words like current Account (Vm)", "Current account (VI)")

for example it would locate words like EUR Current account (VM), GBP Current account (VM). Not just Current account (VM) or (VI). I tried vWords = Array("margin", "standard class", "*current Account (Vm)*", "*Current account (VI)"*)

Using wildcards to find part of the text

but this didnt work

 

[wigi]

Hello

This is more tricky. For example:

Sub wigi()

    Dim i As Long


    Application.ScreenUpdating = False


    On Error Resume Next


    vWords = Array("margin", "standard class", "current Account (VM)", "Current account (VI)")


    With Cells(1).CurrentRegion.Columns(2)
        .Parent.Parent.Names.Add "wigi", vWords
        sq = .Value
        For i = 2 To UBound(sq)
            If IsError(Evaluate("LOOKUP(2^15,SEARCH(wigi,""" & sq(i, 1) & """))")) Then sq(i, 1) = ""
        Next
        .Value = sq
        .SpecialCells(4).EntireRow.Delete
    End With


End Sub