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
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