Sense Check - What is the best way to do this task

[TheRedCardinal]

The main objective I want to achieve is to run through a stack of data (8000 rows and 12 columns) and see if the value of a specific column for each row matches a number of options (ideally to be specified by the user) and if so, to delete that row.

Now I could do this relatively easily using a loop through the spreadsheet and then deleting rows that match conditions.
But this could take some time, so I wanted to use arrays.

My outline plan is this:

1. Load the whole data into an array, called Array 1
2. ReDim a second array to the same size
3. Find the column containing the data test I need
4. Do a For/Next loop using UBound(Array1) and check if the entry at the position matches any of the excluded values
5. If it doesn't, using a second loop, copy it over into the next blank line in Array 2
6. Clear the contents of the worksheet
7. Post Array2 back into the worksheet

My questions are these:

1. Ideally I'd like a list of excluded codes that the user can edit - what is the best way to do this - should I create a range and have each excluded code in a single cell in that range?
2. Or could I have a single cell which contains separated codes, e.g. "EX, EP, ET"
3. Based on option 1 or 2, how do I then write a formula in VBA that checks the value in the array against ALL possible outcomes, without having a heavily nested IF formula
4. Is my outline method above the best approach to this?

I've not provided any code here yet but I'm willing to give it a go (i.e. please don't think I'm asking for anybody to code this up for me) but some help especially with question 3 above would be great!

 

[JGordon11]

With your exclusion criteria in a single comma space delimited cell maybe something like

dim exclude as string, i as long, j as long,  k as long
exclude = range(name or address).value ' name or address of the exclusion criteria
Const col = 5 ' number of the column to check
k =1
for i = 1 to ubound(Array1)
   if instr(", " & exclude & ",",  ", " & Array1(i,col) & ",") = 0 then
       for j = 1 to ubound(Array1,2)
             Array2(k,j) = Array1(i,j)
       next
       k = k+1
   end if
next

 

[Peter_SSs]

This is a slightly different approach, only loading the column to be checked into memory, not the whole array.
I suspect that you will find it plenty fast enough. In fact with my sample data it was about twice as fast as loading the whole data into an Array1 and re-creating the relevant rows in a new Array2 as you had outlined.

I used cell P1 to list the values to be deleted like this

I also assumed a header row.

Sub Del_Rws()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
  Dim sExcl As String
 
  Const ExcludedValuesCell As String = "P1" '<-This cell contains excluded values separated by "|" eg EX|EP|ET
  Const ColToChech As String = "E"          '<-Could instead be found by the code if column varies
 
  sExcl = "|" & Range(ExcludedValuesCell).Value & "|"
  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range(ColToChech & 2, Range(ColToChech & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If InStr(1, sExcl, "|" & a(i, 1) & "|") > 0 Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A2").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub