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

torgeron

New Member
Joined
Feb 28, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 NameEmployee IDCompany NameHire DateJob ProfileLocation NameWorker's Manager
Test 1xABC2
3/13/2023​
AgentRemoteJohn Smith
Test 2xCompany 1
3/13/2023​
RepresentativeRemoteJohn Smith
Test 3xCompany 1
3/13/2023​
RepresentativeCON FLJohn Smith
Test 4xCompany 1
3/13/2023​
RepresentativeRemoteJohn Smith

After

Employee NameEmployee IDCompany NameHire DateJob ProfileLocation NameWorker's Manager
Test 1xABC2
3/13/2023​
AgentRemoteJohn Smith
Test 3xCompany 1
3/13/2023​
RepresentativeCON FLJohn Smith


Any help in this is GREATLY appreciated. Thank you
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
VBA Code:
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
 
Upvote 0
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"?
 
Upvote 0
VBA Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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
Back
Top