Excel VBA Delete the Entire Row if Value Contains the a Value in a range on Another Sheet

Grob

New Member
Joined
Oct 20, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Column C of my [Event History] sheet is my main data source. Column A of my [Eliminate] sheet has a list of different text.
The following works if the in [Eliminate] A1 is MATCHED in [Event History] Column C.
I need to be able to delete entire rows in [Event History] which CONTAINS any of the words in column A of [Eliminate].

Sub DeleteRowIfValue()
'
Dim LastRowInRange As Long, RowCounter As Long
'
LastRowInRange = Sheets("Event History").Range("C1:C100").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row ' Returns a Row Number
'
For RowCounter = LastRowInRange To 1 Step -1 ' Count Backwards
If Sheets("Event History").Range("C" & RowCounter) = Sheets("Eliminate").Range("A1") Then ' If Cell matches our 'Delete if' value then
Sheets("Event History").Rows(RowCounter).EntireRow.Delete ' Delete the row
End If
Next
End Sub

I've tried using wildcard characters in the [Eliminate] list but this did not work.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,068
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Try it like
VBA Code:
If Sheets("Event History").Range("C" & RowCounter).Value Like "*"& Sheets("Eliminate").Range("A1").Value & "*" Then     '   If Cell matches our 'Delete if' value then
 

Grob

New Member
Joined
Oct 20, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Nice. That part works well. My [Eliminate] sheet has a list of multiple text in column A. Can this work till the end of the list is met?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,068
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Grob()
   Dim Ary As Variant
   Dim Cl As Range, Rng As Range
   Dim i As Long
   
   With Sheets("Eliminate")
      Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   With Sheets("Event History")
      For Each Cl In .Range("C1", .Range("C" & Rows.Count).End(xlUp))
         For i = 1 To UBound(Ary)
            If Cl.Value Like "*" & Ary(i, 1) & "*" Then
               If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
               Exit For
            End If
         Next i
      Next Cl
      If Not Rng Is Nothing Then Rng.EntireRow.Delete
   End With
End Sub
But make sure there are no blank cells in the eliminate sheet.
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,068
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,044
Messages
5,767,800
Members
425,437
Latest member
blaix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top