- Mar 26, 2013
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.
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