I am looking for a macro that will delete the entire row if the cells contain any values in a certain list of values. My preference would be to store the list of values on another sheet within the spreadsheet. I have a few things that are making this very complicated.
1. I have multiple sheets which I need to run this macro on, and the column is different on one sheet than it is from the rest. Sheet1 & Sheet2 is column K, Sheet3 is column J. The data will always be in these columns.
2. The list of values I want to delete is pretty long, more than 37 different values (leaving only certain values is not really an option). I will add to this list every week as I run new reports.
I am using the code below right now and I have a seperate macro for each value I want it to delete and I call all of these macros in another macro, this is obviously becoming very cumbersome and I need a simpler solution.
1. I have multiple sheets which I need to run this macro on, and the column is different on one sheet than it is from the rest. Sheet1 & Sheet2 is column K, Sheet3 is column J. The data will always be in these columns.
2. The list of values I want to delete is pretty long, more than 37 different values (leaving only certain values is not really an option). I will add to this list every week as I run new reports.
I am using the code below right now and I have a seperate macro for each value I want it to delete and I call all of these macros in another macro, this is obviously becoming very cumbersome and I need a simpler solution.
Code:
If ActiveSheet.Name = "Tracking System Compliance" Then
Range("J:J").Select
Else: Range("K:K").Select
End If
Dim c As Range
Dim SrchRng
Set SrchRng = Selection
Do
Set c = SrchRng.Find("ACCUT", LookIn:=xlValues, LookAt _
:=xlPart)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing