Fastest way utilizing all cores on the CPU to find and delete rows that don't match criteria?

[thechazm]

Does anyone know what the fastest way utilizing all cores on the CPU to find and delete rows that don't match criteria? Currently I am trying to use an array to remove the items but for 16K+ rows to analyze plus only utilizing one core on the cpu it takes over 20 minutes. Below is my code if anyone can shed some light on it for me please.

Thanks

Function RemoveCrap()
On Error GoTo ErrHandler
FastVBA
Dim xlsSheet As Excel.Worksheet
Set xlsSheet = ActiveWorkbook.Worksheets("NN")
Set rng = xlsSheet.UsedRange
deleterow = False
firstrow = True
For Each r In rng.Rows
    If firstrow = True Then
        firstrow = False
        GoTo Skip
    End If
    For Each s In ShopArray
        If r.Cells(r.Row, 2) = s Then
            deleterow = False
            Exit For
        Else
            deleterow = True
        End If
    Next s
    If deleterow = True Then
        r.EntireRow.Delete
        deleterow = False
    End If
Skip:
Next r
Set xlsSheet = Nothing
SlowVBA
Exit Function
ErrHandler:
MsgBox Err.Number & " - " & Err.Description
SlowVBA
Set xlsSheet = Nothing
 
End Function

Function ShopArray() As Variant
Dim Shops() As Variant
ReDim Shops(1 To 14)
Shops(1) = 11
Shops(2) = 17
Shops(3) = 26
Shops(4) = 31
Shops(5) = 38
Shops(6) = 41
Shops(7) = 51
Shops(8) = 56
Shops(9) = 57
Shops(10) = 64
Shops(11) = 67
Shops(12) = 71
Shops(13) = 72
Shops(14) = 99
ShopArray = Shops
End Function

 

[nuked]

What are you trying to do?

 

[RoryA]

It would probably be much faster to use a formula column to check if the value in B matches one of the values you are interested in, sort that column, then delete all the rows that don't in one hit.

 

[Jonmo1]

I don't know about the "Utilizing all cores" part, that seems more of an OS level Issue..

But it seems to me you've "over engineered" your fairly simple task.
I'm assuming FastVBA and SlowVBA are functions you've created that turn off/on events and calculations and such?

Try this

Sub DeleteJunk()
Dim lr As Long, i As Long
Dim xlsSheet As Worksheet

FastVBA
Set xlsSheet = ActiveWorkbook.Worksheets("NN")
lr = xlsSheet.Cells(Rows.Count, "B").End(xlUp).Row

On Error GoTo ErrHandler
For i = lr To 2 Step -1
    Select Case xlsSheet.Cells(i, "B")
        Case 11, 17, 26, 31, 38, 41, 51, 56, 57, 64, 67, 71, 72, 99
            'Do Nothing
        Case Else
            xlsSheet.Rows(i).EntireRow.Delete
    End Select
Next i

Set xlsSheet = Nothing
SlowVBA
Exit Sub

ErrHandler:
MsgBox Err.Number & " - " & Err.Description
SlowVBA
Set xlsSheet = Nothing
End Sub

 

[nuked]

You can manually assign the number of processors in the main excel options.


Advanced => Check "Enable multi-threaded calcs"
Advanced => Check "Use all processors on this computer"

 

[RoryA]

VBA is single threaded though.

 

[thechazm]

Correct Fast vba and slow vba functions are shown below. I know that vba is single threaded but what I am trying to find out is when you use a formula or certain built in meathods like application.match or forumula's it does use both or all cores of the cpu for processing. It's a lot of data and I'm trying to delete any row that does not match my criteria which is populated by my shoparray function. Anyway here is the code.

Function FastVBA()
Statuslabel.Show
DoEvents: DoEvents: DoEvents
DoEvents: DoEvents: DoEvents
DoEvents: DoEvents: DoEvents
DoEvents: DoEvents: DoEvents
DoEvents: DoEvents: DoEvents
DoEvents: DoEvents: DoEvents
DoEvents: DoEvents: DoEvents
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
End Function

Function SlowVBA()
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Unload Statuslabel
End Function

BTW thanks for the comments everyone just hope there is something I can do to make this faster.

Edit 1: Jonmo1 I am testing that out to see if it will be better. Just the simple fact that it is a case statement will problably make it faster instead of checking each one.

 

[thechazm]

Thanks going to test that out as well RoryA.

 

[thechazm]

@Jonmo1: It took 12 minutes using that function which is better than my 20 but still trying to get it a bit faster.

I think what RoryA suggested would problably be best and while I do know a bit about forumlas in cells I do not know of an efficiant forumula to check for multiple criteria and return a value.

I know I can use MATCH and for each match function I could have it return a 1 or a 0 or whatever but is there a way to use MATCH on a single cell but with multiple checks on that cell?

 

[thechazm]

I would like to apologies firstly because for some reason my browser will not let me edit my comments anymore and I have to post a new one instead... Anyway the following function works but would be a huge formula is there a better way to achive the same functionality as this formula but in a better way?

=IF(ISERROR(MATCH("11",B2,0))=FALSE,1,IF(ISERROR(MATCH("17",B2,0))=FALSE,1,IF(ISERROR(MATCH("26",B2,0))=FALSE,1,IF(ISERROR(MATCH("31",B2,0))=FALSE,1,0))))