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
– PaulFrancis, Commented Mar 17, 2015 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
– 3689, Commented Mar 17, 2015 at 11:03

user4039065 · Accepted Answer · 2015-03-17 11:08:56Z

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, 2015 at 11:08

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

3689 Over a year ago

Thanks Jeeped for going through it. What does If CBool(Application.Subtotal(103, .Cells)) do ? and 103 is constant ?

user4039065 Over a year ago

@Kiran - The worksheet SUBTOTAL function 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.

3689 Over a year ago

Many Thanks Jeeped, That helps. code works perfectly. Have a nice day ahead :)

Community · Accepted Answer · 2017-05-23 11:58:02Z

0

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

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Mar 17, 2015 at 11:32

Our Man in Bananas

6,00525 gold badges104 silver badges158 bronze badges

Collectives™ on Stack Overflow

Deleting rows with specific words with VBA

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related