Forum Discussion
Remove all values except some specific ones
Hi all,
I got the issue, that I have a list with some SKUs (Sheet: "Accessories") in column A, and for each SKU a list of accessories in column B. The task is to remove all accessories except a specific list which is in sheet "Values". Do you have any idea how I can do this? Unfortunately I'm not able to attach the file to the ticket. So here is a download link: https://www.dropbox.com/scl/fi/cic28uycj7jnhem3nbc87/References.xlsx?dl=0&rlkey=3l3a912sxruw4negcke777sb7
Best regards
Jürgen
Juergen_Thoma I've added a PowerQuery solution to your file. I creates a table with all 945 SKU's. Of these, only 150 have accessories that are on the "Values" list.
4 Replies
Run this macro:
Sub RemoveSKU() Dim w As Worksheet Dim m As Long Application.ScreenUpdating = False Set w = Worksheets("Accessories") If w.FilterMode Then w.ShowAllData End If m = w.Range("A" & w.Rows.Count).End(xlUp).Row w.Range("A1:B" & m).AutoFilter Range("C1").EntireColumn.Insert w.Range("C2:C" & m).Formula = "=ISNUMBER(MATCH(A2,Values!A:A,0))" On Error GoTo ExitHere w.Range("A1:C" & m).AutoFilter Field:=3, Criteria1:="=False" w.Range("A2:C" & m).EntireRow.Delete ExitHere: w.Range("A1:C" & m).AutoFilter w.Range("C1").EntireColumn.Delete Application.ScreenUpdating = True End Sub- Hello Hans,
sorry for the late reply. I used that macro, but what it does for me is to remove all SKUs and the accessories-products column.
What I need is to keep all SKUs, but remove all values from the "accessories-products" column, except the ones which are listed in the "Values" sheet. Sorry if my explanation was unclear in the first post.
Best regards
JürgenJuergen_Thoma I've added a PowerQuery solution to your file. I creates a table with all 945 SKU's. Of these, only 150 have accessories that are on the "Values" list.
