Deleting rows that are not on a second list

[kadain]

Hi Guys and Gals
Bit Stumped on wraping my head round how to get his done but basicly am looking to make a macro that would look threw a list of unique code then delete any codes that are not on the list in a complete list
The complete list of products looks a little like this(information changed for Legal Reasons), looking for the user to put in the unque codes in to a second sheet and remove the ones not on the list in the list of products

Sample Wine June22 v1.1.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2
3	Wine Product Price list effective 1st June 2022 (v1.1)
4	Product Code	Product Description		Vegan	Vegetarian	Organic	Product Size	Qty per Case
5
6	Argentina
7		Argentina Red	Region
8	324234234	Ben Marco Pinot Noir 2017	Mendoza	V	V		Bottle	6
9	22244565	Benegas Ataliva Malbec 2021	Mendoza	V	V		Bottle
10	2131311	Benegas Estate Cabernet Franc 2016	Mendoza	V	V		Bottle
11	13131231	Benegas Estate Malbec 2020	Mendoza	V	V		Bottle
12	22423234	Crios Tannat Mendoza Argentina 2018	Mendoza	V	V		Bottle	6
13	35345345	Finca la Nina Malbec 2021	Mendoza				Bottle	12
14	323113	Goyenechea 5th Generacion Malbec Gran Reserva 2014	San Rafael				Bottle	12
15	42414124	Goyenechea Cabernet Sauvignon 2020	San Rafael				Bottle	12
16	32422423	Goyenechea Centenario Malbec Reserva 2019	San Rafael				Bottle	12
17	234234322	Goyenechea Lorenza Bonarda 2017	San Rafael				Bottle	12
18	23423423	Goyenechea Malbec Mendoza 2020	San Rafael				Bottle	12
19	12411424	Goyenechea Syrah 2016	San Rafael				Bottle	12
20	34534534	Hanger And Flank Malbec	Mendoza				24 X Qtr	1
21	14414124	Hanger And Flank Malbec 2020	Mendoza				Bottle	12
22	43534345	Juan Benegas Malbec 2020	Mendoza	V	V		Bottle
23	3342424	Luna Benegas Cabernet Sauvignon 2020	Mendoza	V	V		Bottle
24	534535353	Montanes Malbec Argentina 2020	Mendoza				Bottle	12
25	345353434	Susana Balbo Crios Cabernet Sauvignon	Mendoza	V	V		Bottle	6
26	345345345	Susana Balbo Late Harvest Malbec 2019	Mendoza	V	V		50 Cl	12
27	242423454	Terrazas de Los Andes Select Malbec	Mendoza				Bottle	6
28
29		Argentina Rosé	Region
30	1231313131	Crios Rosé Of Malbec 2019	Mendoza	V	V		Bottle	6
31	3131325	Carmela Benegas Rosé 2021	Mendoza	V	V		Bottle
32	3233131231	Goyenechea Merlot Rosé Mendoza 2020	San Rafael				Bottle	12
33
34		Argentina White	Region
Wine June22 v1.1

Cell Formulas
Range	Formula
H9:H11,H31,H22:H23	H9	=IFERROR(VLOOKUP(A9,'https://inveraritymorton1.sharepoint.com/sites/SalesOps/Shared Documents/Sales Ops Private/Pricing and PINC/PINC/PINC June ''22/Team folder/[Wines June22 v1.1 (Andrew-Aidan) Working Doc.xlsx]Status'!A:G,5,FALSE),"")

i need it not to delete the spaces between the catagory headers and the catagory headers too aswell
bit of a head scratcher for me but am sure the comminty could help out here
i look forward to see what yous come up with
P.S. there is another two colounms i had to remove for legal Reasons thats why there two blanks at the end

 

[Max1616]

I added comments to the code to try and explain each line as best as I could:

Sub Remove()

'The goal of this is to loop through each row, and evaluate the 3 "If" statements below. If any of those fire as True, then delete the row and move to the next row.
'I'm using i as an integer that represents each row as the loop iterates. If the last row used is 55, then i starts as 55, then iterates to 54, then 53, etc... all the way to 1
For i = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1 'Loops through the last row to row 1 (Step -1 means that i will increment by negative 1 each loop)
'In the above line, this code will find the last used row in a column: "Cells(Rows.Count, 2).End(xlUp).Row" - This is looking at column B (2)

'Start of the If Statements

'If Cell Ai is blank and Cell Bi is blank, then delete the row
    If Cells(i, 1) = "" And Cells(i, 2) = "" Then
        Cells(i, 2).EntireRow.Delete 'Delete the row
        GoTo Nexti 'if the above if statement is true, once the row is deleted this line will force the code to move to the next iteration of the loop (it skips to "Nexti")
    End If

'If Cell Ai is blank, Cell Bi is not blank, Cell Bi is bolded and cell Ai+1 is either Bold or blank then delete the row
    If Cells(i, 1) = "" And Cells(i, 2) <> "" And Cells(i, 2).Font.Bold = True And (Cells(i + 1, 1).Font.Bold = True Or Cells(i + 1, 1) = "") Then
        Cells(i, 2).EntireRow.Delete
        GoTo Nexti
    End If
   
'If Cell Ai is blank, Cell Ai is Bold, and cell Ai+1 is Blank then delete the row
    If Cells(i, 1) <> "" And Cells(i, 1).Font.Bold = True And Cells(i + 2, 1) = "" Then
        Cells(i, 2).EntireRow.Delete
        GoTo Nexti
    End If
   
Nexti: 'This is where "GOTO Nexti" is skipping to
Next i 'Iterate to the next i and restart the loop

End Sub

I hope this helps

 

[kadain]

I added comments to the code to try and explain each line as best as I could:

Sub Remove()

'The goal of this is to loop through each row, and evaluate the 3 "If" statements below. If any of those fire as True, then delete the row and move to the next row.
'I'm using i as an integer that represents each row as the loop iterates. If the last row used is 55, then i starts as 55, then iterates to 54, then 53, etc... all the way to 1
For i = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1 'Loops through the last row to row 1 (Step -1 means that i will increment by negative 1 each loop)
'In the above line, this code will find the last used row in a column: "Cells(Rows.Count, 2).End(xlUp).Row" - This is looking at column B (2)

'Start of the If Statements

'If Cell Ai is blank and Cell Bi is blank, then delete the row
    If Cells(i, 1) = "" And Cells(i, 2) = "" Then
        Cells(i, 2).EntireRow.Delete 'Delete the row
        GoTo Nexti 'if the above if statement is true, once the row is deleted this line will force the code to move to the next iteration of the loop (it skips to "Nexti")
    End If

'If Cell Ai is blank, Cell Bi is not blank, Cell Bi is bolded and cell Ai+1 is either Bold or blank then delete the row
    If Cells(i, 1) = "" And Cells(i, 2) <> "" And Cells(i, 2).Font.Bold = True And (Cells(i + 1, 1).Font.Bold = True Or Cells(i + 1, 1) = "") Then
        Cells(i, 2).EntireRow.Delete
        GoTo Nexti
    End If
  
'If Cell Ai is blank, Cell Ai is Bold, and cell Ai+1 is Blank then delete the row
    If Cells(i, 1) <> "" And Cells(i, 1).Font.Bold = True And Cells(i + 2, 1) = "" Then
        Cells(i, 2).EntireRow.Delete
        GoTo Nexti
    End If
  
Nexti: 'This is where "GOTO Nexti" is skipping to
Next i 'Iterate to the next i and restart the loop

End Sub

I hope this helps

appriecated muchly, this will help my future endevours in VBA