VBA Code problem

[frets]

Can anyone explain why the section i have highlighted in red will not work? Or can anyone make this code simpler and run faster with over 600,000 rows in data?
Thanks

Sub Delete_Based_on_Remove_Years()

' This macro will delete an entire row based on the presence of a
'predefined word or set of words.  If that word or set of words is
'found in a cell, in a specified column, the entire row will be 'deleted

Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim FoundRowToDelete As Boolean
Dim OriginalCalculationMode As Long
Dim RowsToDelete As Range
Dim SearchItems() As String

Dim DataStartRow As Long
Dim SearchColumn As String
Dim SheetName As String

 

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Choose the row you want the search and delete to start on
' Choose the column to search and delete to use for deletion
' Choose the sheet in the workbook you want this macro to be run on

DataStartRow = 1
SearchColumn = "b"
SheetName = "Sheet1"

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Enter the terms you want to be used for criteria for deletion
' All terms entered below are CASE SENSITIVE and need to be
'seperated by a comma

SearchItems = Split("1994, 1995, 1996, 1997")


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

On Error GoTo Whoops
OriginalCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, SearchColumn).End(xlUp).Row
For X = LastRow To DataStartRow Step -1
FoundRowToDelete = False
For Z = 0 To UBound(SearchItems)
If InStr(.Cells(X, SearchColumn).Value, SearchItems(Z)) Then
FoundRowToDelete = True
Exit For
End If

Next

If FoundRowToDelete Then
If RowsToDelete Is Nothing Then
Set RowsToDelete = .Cells(X, SearchColumn)
Else
Set RowsToDelete = Union(RowsToDelete, .Cells(X, SearchColumn))
End If

If RowsToDelete.Areas.Count > 100 Then
RowsToDelete.EntireRow.Delete
Set RowsToDelete = Nothing
End If
End If

Next

End With
If Not RowsToDelete Is Nothing Then
RowsToDelete.EntireRow.Delete
End If

Whoops:
Application.Calculation = OriginalCalculationMode
Application.ScreenUpdating = True

 
End Sub

 

[Snakehips]

frets,

Just a quick thought....
SearchItems = Split("1994, 1995, 1996, 1997")
Because you are not specifying a delimiter in the Split function, it will assume the default delimiter as a space, " ".
So your SearchItems strings will be "1994," "1995," "1996," & "1997"
I assume that you don't want the trailing comma ?

If so, then try = Split("1994 1995 1996 1997")

Hope that helps.

 

[xenou]

Duplicate: http://www.mrexcel.com/forum/excel-...applications-code-problem-takes-long-run.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).