remove rows which contain words from range on other sheet

[liwi2]

Hi All,
I need to exclude rows from ie sheet1.Range(A1:A200) which contain keywords from cells in sheet2.Range("I2:I50").
At the moment I am using array but I want the words to be taken from range in case user what to change these keywords.

Could anyone help me with this?
Thank you

Code which uses array
Sub test()
....
Dim arrWords
Dim rng as Range
Dim xlCalc As XlCalculation
arrWords = Array("A","B","C") ' edit the array as required
xlCalc = Application.Calculation
Set rng = Range("A1:A200")

For rw = rng.Rows(rng.Rows.Count).Row To rng.Rows(1).Row Step -1
For j = 0 To UBound(arrWords)
If InStr(1, rng(rw, 1), arrWords(j), vbTextCompare) Then
bDel = True
rng.Parent.Rows(rw).EntireRow.Delete
Exit For
End If
Next
Next
Application.Calculation = xlCalc

....
End Sub

 

[Fishboy]

Hi All,
I need to exclude rows from ie sheet1.Range(A1:A200) which contain keywords from cells in sheet2.Range("I2:I50").
At the moment I am using array but I want the words to be taken from range in case user what to change these keywords.

Could anyone help me with this?
Thank you

Code which uses array
Sub test()
....
Dim arrWords
Dim rng as Range
Dim xlCalc As XlCalculation
arrWords = Array("A","B","C") ' edit the array as required
xlCalc = Application.Calculation
Set rng = Range("A1:A200")

For rw = rng.Rows(rng.Rows.Count).Row To rng.Rows(1).Row Step -1
For j = 0 To UBound(arrWords)
If InStr(1, rng(rw, 1), arrWords(j), vbTextCompare) Then
bDel = True
rng.Parent.Rows(rw).EntireRow.Delete
Exit For
End If
Next
Next
Application.Calculation = xlCalc

....
End Sub

Hi liwi2, is this any good to you? Remember to test it out in a COPY of your workbook:

Sub DeleteRowsWithMatchingKeywords()
Dim X As Long, LR As Long, LR2 As Long
Dim cRange As Range, sRange As Range


LR = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set cRange = Sheets("Sheet1").Range("A1:A" & LR)


LR2 = Sheets("Sheet2").Cells(Rows.Count, "I").End(xlUp).Row
Set sRange = Sheets("Sheet2").Range("I2:I" & LR2)


For X = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(X)
        If Application.WorksheetFunction.CountIf(sRange, .Value) Then
            .EntireRow.Delete
        End If
    End With
Next X


End Sub

 

[liwi2]

Perfection. Works like a charm. Thank you so much.

Would you be so kind and walk me through this part of the code?
For X = cRange.Cells.Count To 1 Step -1
With cRange.Cells(X)
If Application.WorksheetFunction.CountIf(sRange, .Value) Then

 

[Fishboy]

Perfection. Works like a charm. Thank you so much.

Would you be so kind and walk me through this part of the code?
For X = cRange.Cells.Count To 1 Step -1
With cRange.Cells(X)
If Application.WorksheetFunction.CountIf(sRange, .Value) Then

Happy to help. To answer your questions...

"For X = cRange.Cells.Count To 1 Step -1" means that we will loop for X number of times. X is set to how many cells are in the check range (cRange). As we want to be deleting rows we need to start at the bottom and work upwards otherwise the "end of the range" keeps moving as rows are deleted and you will end up skipping out rows. So in effect we are looping from the higher number (how many cells are in the range to be checked) stepping backwards to 1.

"With cRange.Cells(X)" means the row we are currently looking at in the check range, which is defined by X. So if we are on row 200 then X would be 200 in which case cRange.Cells(X) essentially means Cell 200 at this point. When we step back to X=199 then we move up to row 199, and so on.

"If Application.WorksheetFunction.CountIf(sRange, .Value) Then" is basically doing a COUNTIF based on the current cell value. If the current cell value exists in the search range (sRange) on Sheet2 then Application.WorksheetFunction.CountIf(sRange, .Value) would be True, so we know that row can be deleted and we can move up to the next row above.

I hope this helps explain things a little better for you.

 

[liwi2]

That's clever. I wouldn't think of stepping back but it makes sense once you explained it Thank you once more.

 

[Fishboy]

That's clever. I wouldn't think of stepping back but it makes sense once you explained it Thank you once more.

You're most welcome.