VBA: Loop through an string array of variants and delete rows if matching value

Question

I've got multiple text variables (fruits) that when and if they appear on column R I need to delete the entire row. I've got the following code, but as I have 15 variables of "Fruit" (and may need to add more in the future) I was wondering how I could make this is a single subroutine with a loop. I tried many ways failing miserably. Possibly with a string array including all the names?

Thanks in advance,

Sub Fix1()
Dim Fruit As String

Fruit = "Apple"

Do
On Error GoTo ByeBye
Range("R:R").Find(Fruit).EntireRow.Delete
Loop
ByeBye:
Exit Sub
End Sub



Sub Fix2()
Dim Fruit As String

Fruit = "Orange"
Do
On Error GoTo ByeBye
Range("R:R").Find(Fruit).EntireRow.Delete
Loop
ByeBye:
Exit Sub
End Sub

user3598756 · Accepted Answer · 2017-02-15 17:26:39Z

2

you could use AutoFiter():

Sub FixAll()
    Dim Fruits As Variant

    Fruits = Array("Apple", "Banana", "Pear") '<--| list your fruits
    With Range("R:R") '<--| reference your range
        .AutoFilter Field:=1, Criteria1:=Fruits, Operator:=xlFilterValues '<--| filter referenced column with 'Fruits'" content
        If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete '<--| if any cell found other than header one then delete its corresponding row
    End With
    ActiveSheet.AutoFilterMode = False
End Sub

edited Feb 15, 2017 at 17:26

answered Feb 15, 2017 at 13:47

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

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

1 Comment

Jonorl Over a year ago

Amazing, it worked! I don't know why I thought that autofilter would result in an error if any of the values were missing, but seems to be working fine. Many thanks for your help!

Slai · Accepted Answer · 2017-02-15 14:30:57Z

0

If you don't need the first row deleted:

Dim r As Range
Set r = ActiveSheet.UsedRange

r.AutoFilter 19 - r.Column, Array("Apple", "Orange"), xlFilterValues 
r.Offset(1).Delete xlShiftUp                               ' deletes the non-filtered rows
r.AutoFilter                                               ' hide the filter

answered Feb 15, 2017 at 14:30

Slai

23k5 gold badges49 silver badges55 bronze badges

Collectives™ on Stack Overflow

VBA: Loop through an string array of variants and delete rows if matching value

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related