Delete row if one of the specified values are not identified on that row, column A

If you have a large list or dataset and need to keep particular rows of data rather than select those you don’t want you can select the ones you want to keep and use this example below to create a script to remove all but these values specified.

Adjust the code going from the bottom row to the top, and the values of the items you wish to keep. This particular code is taken as an example from a recent project whereby a client who sold thousands of items on Amazon wanted to remove all but specific brands.

Datanology are data experts and can help you accomplish any data project or task. Feel free to contact us using the form or ask a question about this example script.


For n = 100 To 1 Step -1
If ActiveSheet.Range("A" & n) <> "Cheese" And _
ActiveSheet.Range("A" & n) <> "Milk" And _
ActiveSheet.Range("A" & n) <> "Bread" Then
ActiveSheet.Range("A" & n).EntireRow.Delete shift:=xlUp
End If
Next n



'Actual code used for removing all products on an Amazon upload that arent in the list/code below

With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For n = lastrow To 1 Step -1
If ActiveSheet.Range("A" & n) <> "BROTHER" And _
ActiveSheet.Range("A" & n) <> "Canon " And ActiveSheet.Range("A" & n) <> "DELL" And ActiveSheet.Range("A" & n) <> "ECO" And ActiveSheet.Range("A" & n) <> "EPSON" And ActiveSheet.Range("A" & n) <> "FUJI" And _
ActiveSheet.Range("A" & n) <> "HP" And ActiveSheet.Range("A" & n) <> "INFOTEC" And ActiveSheet.Range("A" & n) <> "INTEGRA" And ActiveSheet.Range("A" & n) <> "KATUN" And ActiveSheet.Range("A" & n) <> "KODAK " And _
ActiveSheet.Range("A" & n) <> "KONICA" And ActiveSheet.Range("A" & n) <> "KYOCERA" And ActiveSheet.Range("A" & n) <> "LEXMARK" And ActiveSheet.Range("A" & n) <> "MEDIA SCIENCE- EDIT NAME" And ActiveSheet.Range("A" & n) <> "MINOLTA" And _
ActiveSheet.Range("A" & n) <> "OKI" And ActiveSheet.Range("A" & n) <> "PANASONIC" And ActiveSheet.Range("A" & n) <> "Perfect GREEN  - EDIT NAME" And ActiveSheet.Range("A" & n) <> "QMS" And ActiveSheet.Range("A" & n) <> "RICOH" And _
ActiveSheet.Range("A" & n) <> "RICOH" And ActiveSheet.Range("A" & n) <> "SAMSUNG " And ActiveSheet.Range("A" & n) <> "SANDISK" And ActiveSheet.Range("A" & n) <> "SEAGATE" And ActiveSheet.Range("A" & n) <> "SHARP" And _
ActiveSheet.Range("A" & n) <> "TOSHIBA" And _
ActiveSheet.Range("A" & n) <> "XEROX" Then
ActiveSheet.Range("A" & n).EntireRow.Delete shift:=xlUp
End If
Next n






Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *