Delete Lines that possess one of the criteria in this list

[B5rocksass]

I have a report that i run for departmental metrics but the report has some lines that should not be factoring in. I would like to remove them with a macro, but i'm unsure if I'll get consistent results if I record it as 'filtering to delete for each criteria' in the list. There has to be a better way. Please note that all the criteria will only ever be found in one particular column. I mocked up my example and added a small list to delete the lines with that criteria. I'm hoping someone can help me design the macro. I don't know much about VBA unfortunately. Please advise.

Test.xlsm
	A	B	C	D	E	F
1	PO Number	PO Date	Vendor Name	Vendor #	Item Number	Item Description
2	222987	1/22/2019	NORTH AMERICA	160342	4300	5.905DIAMETER X18.504WI
3	229138	11/8/2019	VISION TECHNOLOGIES	156851	5000-UMLK-1	Item-1
4	231178	2/21/2020	VISION TECHNOLOGIES	156851	5000-UMLK-5	Item-5
5	235726	10/8/2020	TRUST CORPORATION	162710	RS-SAAS-MOOR	CLOUD LICENSE
6	235726	10/8/2020	TRUST CORPORATION	162710	RS-SAAS-WB	CLOUD LICENSE
7	237591	1/25/2021	SET-RITE OF BONNEVILLE	121671	D0390013Z	Item-1
8	238066	2/12/2021	SET-RITE OF BONNEVILLE	161963	D0390013Z	Item-3
9	239023	3/29/2021	NORTH AMERICA,INC.	968081	89.429.10	varnish
10	239082	3/31/2021	ELECTRONICS FOR ALL	155813	70970519	CONNECTOR 90 DEG 4 PIN SCR
11	239575	4/21/2021	CRANE-IT	161597	CRANE INSPECT	ANNUAL INSPECTION
12	239575	4/21/2021	CRANE-IT	161597	TRUCK CHARGE	TRUCK CHARGE
13	239719	4/27/2021	ELECTRONICS FOR ALL	155813	71093991	PANEL MOUTN RECEPTABLE KIT FEM
14	239737	4/28/2021	ELECTRONICS FOR ALL	155813	70007012	LIGHT BLOCK ASSEMBLY 110.120VA
15	239857	5/3/2021	ACTEGA NORTH AMERICA,INC.	968081	10003698	varnish
16	239885	5/4/2021	ELECTRONICS FOR ALL	155813	70008931	ELECTRIC-LIGHT MODLE
17	239920	5/5/2021	ELECTRONICS FOR ALL	155813	70176810	CONTACT REMOVAL KIT
18	239922	5/5/2021	WIPERS FOR YOU	156181	50LBS	WHITE SHEETING
19	239933	5/5/2021	MARO INDUSTRIES, INC.	163235	P4349	FIRE EXTINGUISHER (DO NO BLOCK)
20	239933	5/5/2021	MARO INDUSTRIES, INC.	163235	C2226-CE	CUSTOM FLOOR SIGN
21	240004	5/7/2021	AKLODA INC	916140	546150	15X15X6 CORRUGATE
22
23				Vendor# to Remove
24				160342
25				156851
26				162710
27				121671
28				161963
Test

 

[JoeMo]

Expand the col D range to cover all your data. Remove or move the target Vendor# List from col D.

Sub RemoveVendors()
Dim R As Range, Remove As Variant
Set R = Range("D2:D36") 'change range to suit
Remove = Array(160342, 156851, 162710, 121671, 161963)
For i = LBound(Remove) To UBound(Remove)
    R.Replace Remove(i), replacement:="#N/A"
Next i
On Error Resume Next
R.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub

 

[B5rocksass]

Hi Joe! Thank you for looking at this and offering your resolution.
I erased the target vendor list as you explained...but I honestly do not know what to do with your code, since I have never learned VBA. How do i create a macro with copy and paste code?

 

[JoeMo]

Hi Joe! Thank you for looking at this and offering your resolution.
I erased the target vendor list as you explained...but I honestly do not know what to do with your code, since I have never learned VBA. How do i create a macro with copy and paste code?

To install standard module code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.

 

[B5rocksass]

Okay, almost there...it threw an error on the range. How to i update the code to look for all rows in the entire column D? I'm asking since every report has either more or less rows.

 

[JoeMo]

Okay, almost there...it threw an error on the range. How to i update the code to look for all rows in the entire column D? I'm asking since every report has either more or less rows.

Replace the existing code with this:

Sub RemoveVendors()
Dim R As Range, Remove As Variant
Set R = Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
Remove = Array(160342, 156851, 162710, 121671, 161963)
For i = LBound(Remove) To UBound(Remove)
    R.Replace Remove(i), replacement:="#N/A"
Next i
On Error Resume Next
R.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub

 

[B5rocksass]

That worked great!
Now that the macro is made, how do i make it available to any report as opposed to just this one?

 

[JoeMo]

That worked great!
Now that the macro is made, how do i make it available to any report as opposed to just this one?

You can run it on any data set you have after you make any needed changes to the vendor ID column and/or the vendor Id's you wish to remove.

 

[B5rocksass]

Thank you so much!

 

[JoeMo]

Thank you so much!

You are welcome -thanks for the reply.