Forum Discussion
Solved
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 s...
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.
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ürgen
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ü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.
- Hi Riny,
that works perfectly, thanks a lot.
Best regards
Jürgen