Deleting rows based on a condition, and the condition is an array

Question

I have 2 columns and 300k rows

The first column has 300 different values, and I want to delete all rows which contain specific values. Currently I have;

Sub DeleteRowsBasedOnCondition()
N = Cells(Rows.Count, 1).End(xlUp).Row
For i = N To 1 Step -1
    If Cells(i, 1).Value = "F1" Then Rows(i).Delete
Next i
End Sub

and it removes a single conndition (F1), but this will take a long time to delete 56 conditions.

I have a named list, toRemove, which contains all the fields I want to remove. ie (F1, F2, F4, F9). I would like to refer to this named list instead of running the code 56 times.

I would like

Sub DeleteRowsBasedOnCondition()
N = Cells(Rows.Count, 1).End(xlUp).Row
For i = N To 1 Step -1
    If Cells(i, 1).Value has a value in the array toRemove
    Then Rows(i).Delete
Next i
End Sub

I tried How to search for string in an array post, but get the following error when referring to the line

function Filter(arr, stringToBeFound):
Run-Time error '13':

Type mismatch

Here is the code I tried:

Sub DeleteRowsBasedOnArrayCondition()
' toremove is a named list of values which are to be compared with and deleted from table
N = Cells(Rows.Count, 1).End(xlUp).Row
For i = N To 1 Step -1
    If IsInArray(Cells(i, 2).Value, toremove) Then Rows(i).Delete
Next i
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Can you post your `toremove` code, the `IsInArray Function` seems to work has intended, it's probable that is the values or type of list that give you a type mismatch. — Miguel_Ryu, Jan 26 '18 at 12:53
Can you just use autofilter and feed it with your array? Then delete visible rows. — brainac, Jan 26 '18 at 13:50
toremove is a list I highlighted and named. It is a list of strings. — frank, Jan 26 '18 at 14:19
Try this. change your `toremove` into `toremove = Array("F1", "F2", "F4", "F9", .....)` and see if you have the same error, I suspect that your IsInArray does not support the type of list you're trying to use. — Miguel_Ryu, Jan 26 '18 at 14:26

score 0 · Answer 1 · answered Jan 26 '18 at 14:39

Following brainac's suggestion, I filtered by toRemove list, then deleted the rows.

Sub DeleteRowsBasedOnCondition()
For Each i In Worksheets("Sheet2").Range("toRemove")
    Range("a1").Select
    ActiveSheet.Range("$A$1:$c$9").AutoFilter Field:=1, Criteria1:=i
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).Select
    Selection.EntireRow.Delete
Next i
ActiveSheet.ShowAllData

End Sub

Deleting rows based on a condition, and the condition is an array

1 Answers1