Deleting rows with specific words with VBA

Question

currently my excel data consists of specific words and #N/A, words are like "build One" "proj ex".. I have prepared a code in which it only deletes one condition but I want it for many words. below is my code. Any help is welcome. Thanks.

Sub del()
Dim rng1 As Range
Set rng1 = Range([B2], Cells(Rows.Count, "B").End(xlUp))
ActiveSheet.AutoFilterMode = False
With rng1
.AutoFilter Field:=1, Criteria:=("#N/A")
.Delete xlUp
End With
End Sub

You could create an array of all words you want, then loop the array. # — PaulFrancis, Mar 17 '15 at 10:58
Hey Paul, I am new to VBA, will be great if you can give me an e.g of it looping it in array. — 3689, Mar 17 '15 at 11:03

score 4 · Answer 1 · answered Mar 17 '15 at 11:08

4

Use a variant array as a constructor for your word list.

Sub del()
    Dim rng1 As Range, vDELs As Variant

    vDELs = Array("#N/A", "proj ex", "build One")
    Set rng1 = Range([B2], Cells(Rows.Count, "B").End(xlUp))

    ActiveSheet.AutoFilterMode = False
    With rng1
        .AutoFilter Field:=1, Criteria1:=(vDELs), Operator:=xlFilterValues
        With .Offset(1, 0)
            If CBool(Application.Subtotal(103, .Cells)) Then _
                .EntireRow.Delete
        End With
        .AutoFilter
    End With
End Sub

Good catch on bracketing the array in Criteria1:=(vDELs). That is important. Also a good idea to check if you have rows to delete before committing to the operation.

answered Mar 17 '15 at 11:08

Thanks Jeeped for going through it. What does If CBool(Application.Subtotal(103, .Cells)) do ? and 103 is constant ? – 3689 Mar 17 '15 at 11:13
2

@Kiran - The worksheet [SUBTOTAL function](https://support.office.com/en-us/article/SUBTOTAL-function-e27c301c-be9a-458b-9d12-b9a2ce3c62af) does not count hidden values so if it counts anything it will return a non-zero which `CBool` interprets as *True*. The **103** is the designation for Subtotal to act as CountA. See the supplied link for more info. – Mar 17 '15 at 11:19
Many Thanks Jeeped, That helps. code works perfectly. Have a nice day ahead :) – 3689 Mar 17 '15 at 11:32

score 0 · Answer 2 · edited May 23 '17 at 11:58

you could try something like:

sFormula = "=IF(OR(ISERROR(B:B),B:B=""proj ex"", B:B=""build One""),NA(),"""")"

Set rng1 = Range("A2:A" & Cells(Rows.Count, "B").End(xlUp).Row)
rng1.Formula = sFormula

' Now use SpecialCells to remove the rows:

rng1.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete shift:=xlUp

more on this type of technique at SO: How to delete multiple rows in Excel without a loop and Ron de Bruin Excel VBA Top Banana: Special Cells limit bug

Deleting rows with specific words with VBA

2 Answers2