macro - search and delete

Welcome to the forum.

It would be good if you post your code within code tags in the future.

You can list the words in one column of your macro file and then use a loop to go through each word and perform the necessary actions. This way, you wouldnt need to code it again and again.

Let me know if you need more help with this.

I have 2 sheets - in sheet1, i have put some words starting from A1 to A5. In sheet 2, i have a list of words in column C. I have used this code for this example - you can adapt it to your situation -

Option Explicit
Dim foundcell As Range
Dim lrow As Long
Dim i As Long

Sub find_values()

lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lrow
    Set foundcell = Worksheets(2).Range("C:C").Find(what:=Worksheets(1).Range("A" & i).Value)
    If Not foundcell Is Nothing Then foundcell.EntireRow.Delete
Next i

End Sub

It will automatically take the words 1 by 1 from sheet1 and locate them in sheet2 and then delete the rows where the words are found.

Hello!
Thanks very much for the help!
As smuzoen said there are more than one occurence for every word... so I tried the second code and it's working!

Now I would like to have an "half way"... the second code, but I'd like to take the array from a column in a second worksheet of my file called categories.

I am trying this code but I have an error message "mismatch"...
I am trying to study how to create arrays and so on... in the meanwhile if you can see the error would be really nice!!
thanks!!!

Option Explicit
Sub CancellaTest()

Dim fCell As Range, lCell As Range, fAdd As String, a
Dim wdSrch, keyWd As String, k As Long, nCell As Range, x As Long
Dim First As Integer, last As Integer, i As Integer, j As Integer, temp As Long
ReDim a(x)
With Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    Set lCell = .Cells(.Cells.Count)
End With


Dim cell As Range
Dim val As Variant
 
For Each cell In Worksheets("categories").Range("A1:A10")
     
    val = cell.Value
Next cell



For k = LBound(val) To UBound(val)
keyWd = CStr(val(k))
Set fCell = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row).Find(what:=keyWd, after:=lCell)
If Not fCell Is Nothing Then
    fAdd = fCell.Address
End If
Do Until fCell Is Nothing
ReDim Preserve a(x)
a(x) = fCell.Row
x = x + 1
Set fCell = Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row).FindNext(after:=fCell)
    If fCell.Address = fAdd Then
        Exit Do
    End If
Loop
Next
    For i = LBound(a) To UBound(a) - 1
        For j = i + 1 To UBound(a)
            If a(i) > a(j) Then
                temp = a(j)
                a(j) = a(i)
                a(i) = temp
            End If
        Next j
    Next i
For k = UBound(a) To LBound(a) Step -1
Rows(a(k)).EntireRow.Delete Shift:=xlUp
Next
End Sub

I was not sure if you sorted it - If you place the list of words in column A in sheet 2 that you want to search for you can create an array from this - then compare this list of key words against Column A in sheet 1 that is the list that you want to check for keywords

Option Explicit

Sub setRNG()
Dim fCell As Range, lCell As Range, fAdd As String, a, wordList As Range
Dim wdSrch, keyWd As String, k As Long, nCell As Range, x As Long
Dim First As Integer, last As Integer, i As Integer, j As Integer, temp As Long
ReDim a(x)
With Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    Set lCell = .Cells(.Cells.Count)
End With
Set wordList = Worksheets("Sheet2").Range("A1:A" & Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row)
wdSrch = wordList
For k = LBound(wdSrch, 1) To UBound(wdSrch, 1)
keyWd = CStr(wdSrch(k, 1))
Set fCell = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Find(what:=keyWd, after:=lCell)
If Not fCell Is Nothing Then
    fAdd = fCell.Address
End If
Do Until fCell Is Nothing
ReDim Preserve a(x)
a(x) = fCell.Row
x = x + 1
Set fCell = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).FindNext(after:=fCell)
    If fCell.Address = fAdd Then
        Exit Do
    End If
Loop
Next
    For i = LBound(a) To UBound(a) - 1
        For j = i + 1 To UBound(a)
            If a(i) > a(j) Then
                temp = a(j)
                a(j) = a(i)
                a(i) = temp
            End If
        Next j
    Next i
For k = UBound(a) To LBound(a) Step -1
Rows(a(k)).EntireRow.Delete Shift:=xlUp
Next
End Sub

If you need any help or other questions just ask.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

Wow, this seem alot of code for the job.

' open workbook with searchwords
Application.Workbooks.Open ("Your file path")

' create array holding values from that workbook
arr = Workbook("Your workbook Name").Sheets(2).Range("A1:A50").value

' Close workbook
Workbook("Your workbook Name").Close

' Begin loop
For i = 1 to ubound(arr)
    Do
         Sheets(1).Columns(1).Find(arr(i), , xlValues,xlWhole).EntireRow.Delete
    Loop until err.number <> 0
next i

No more is needed and even less if you have your validation list in the same workbook or a txt file

Try the attached workbook - if you can get Steffens code to work by all means shorter code is better. Anyway see attached workbook.