Continue code on next line: Compile Error: expected list separator or )

I am currently using a macro to search my spreadsheet for terms and if it finds them to delete the entire row.

This has worked fine up until i hit the 1024 character line limit.

I have tried using &_ as well as _ to bring continue the code on the next line and get "Compile Error: expected list separator or )"

Showing only 3 terms but it continues past 1024 characters....

SearchItems = Split("Search Term 1,Search Term 2,Search Term 2,Search Term 3", ",")

As Always Thanks In Advance!

Also here if full code.

' 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

Application.ScreenUpdating = False

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 = "A"
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("Search Term 1,Search Term 2,Search Term 2,Search Term 3", ",")


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



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

so create a sheet that i can inject into my report that has each of my terms listed in column A. next have code that searched that sheet and compares it to column A on my current sheet and delete rows that match. After that is run i can then remove the sheet from the report.

I can figure it all out except the code to Search column A on Sheet2 and if it finds a match in Column A on Sheet1 then delete entire row.

Hello there,

Is this code only being used by you? The reason I ask is because it might be easier to provide the user with an input box which asks the user to type the value they want to search for and delete. You could use a yes no messagebox with this that would ask if the user wants to search another value where if yes is selected to provided the inputbox again if no is selected exit the code.

Thanks!

i dont think a messagebox will work because there are a lot of search terms like 30 now

Hello there,

Try the following. First select the worksheet in which the values you are searching for are in column A

Dim c As Range, Lr As String, FC As Range, lCount As Long   'declare variables

Lr = Range("A6555").End(xlUp).Row   'set lr = to the last row in column A of the active worksheet that contains a value

For Each c In Range("A1:A" & Lr)    'loop through cell in column A from row 1 to lr (defined above)

    On Error Resume Next    'on error keep going

    With Sheets("Sheet2")   'with the worksheet Sheet2
        
        If WorksheetFunction.CountIf(.Columns(1), c.Value) > 0 Then 'if there current cell in the loop's value is found in sheet2 then
            Set FC = .Range("A1")   '   set fc equal to range A1
            
            For lCount = 1 To WorksheetFunction.CountIf(.Columns(1), c.Value)   'loop through lcount from 1 to the number of time the current cell in the loops value is found in sheet2
            
                'set fc equal to the found cell whose value is equal to the current cell in the loop's value
                Set FC = .Columns(1).Find(What:=c.Value, After:=FC, _
                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)

                    On Error GoTo 0
            
                    FC.EntireRow.Delete 'delete the found cell's row
            
            Next lCount 'move to next lcount
            
        End If  'end if statement
        
    End With
Next c  'move to next cell in the c loop

Worked perfect! I have added to both your reps!

Thank you for the star tap!