VBA code to delete certain rows that do not contain info needed

[torgeron]

good morning, I am looking for help on a VBA macro that will allow me to keep certain rows that contain a certain word in the column and delete the other rows. In column F (location name) we want to keep anything that has "CON" in it and in column C (company name) anything that has ABC2 in it. If it does not meet one of those two criteria then can be deleted.

BEFORE

Employee Name	Employee ID	Company Name	Hire Date	Job Profile	Location Name	Worker's Manager
Test 1	x	ABC2	3/13/2023​	Agent	Remote	John Smith
Test 2	x	Company 1	3/13/2023​	Representative	Remote	John Smith
Test 3	x	Company 1	3/13/2023​	Representative	CON FL	John Smith
Test 4	x	Company 1	3/13/2023​	Representative	Remote	John Smith

After

Employee Name	Employee ID	Company Name	Hire Date	Job Profile	Location Name	Worker's Manager
Test 1	x	ABC2	3/13/2023​	Agent	Remote	John Smith
Test 3	x	Company 1	3/13/2023​	Representative	CON FL	John Smith

​

Any help in this is GREATLY appreciated. Thank you

 

[iggydarsa]

Sub MacroX()
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        fnd = InStr(1, UCase(Range("F" & i).Value), UCase("con")) + InStr(1, UCase(Range("C" & i).Value), UCase("abc"))
        If fnd = 0 Then
            Range("A" & i).EntireRow.Delete
        End If
    Next i
End Sub

 

[torgeron]

I tried that, but the issue is it kept all the ones with CON in that location name column but not the ones with ABC2 in the Company name column... I need to keep them both even though they both not have it. Also, what if I need under location name anything that has "CON" in it OR "CW"?

 

[iggydarsa]

Sub MacroX()
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        fnd = InStr(1, UCase(Range("F" & i).Value), UCase("con")) + InStr(1, UCase(Range("F" & i).Value), UCase("cw")) + InStr(1, UCase(Range("C" & i).Value), UCase("abc"))
        If fnd = 0 Then
            Range("A" & i).EntireRow.Delete
        End If
    Next i
End Sub

 

[iggydarsa]

are you sure the company name is under column C?

 

[torgeron]

that WORKED... thank you soooo much! it was my fault, there was a space between the ABC and the number so it was deleting those. Thank you again