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

thechazm

New Member
Joined
Mar 26, 2013
Messages
14
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

Code:
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
 
@Jonmo1: It took 12 minutes using that function which is better than my 20 but still trying to get it a bit faster.
12 minutes really?
I populated 20K rows from col A to Z with random stuff
Put random numbers from 1 to 100 in Column B

The code ran in about 2 minutes.


As far as the formula to test, you can use something like

=OR(B2={11, 17, 26, 31, 38, 41, 51, 56, 57, 64, 67, 71, 72, 99})

that will give you a True/False that you can Filter on.
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Thanks for the help Jonmo1. Yes unfortunatly the computers I get to deal with are a real piece of .... The processor is a Intel Core 2 Duo E6550 @ 2.33 Ghz. While that does not sound to slow the floating point operations on these computers suck so any computing at all is pretty rough.

That's what I was looking for and I appreciate the help. I wanted to do it completly inside of vba but it looks like I'll have to use a mix to get the speed I need.

Thanks again!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top