Macro to delete rows based on muliple values (300+) in a column.

[dboatswain]

Hi everyone,

In the past I have used the formula below to delete rows based on particular values.

Sub Two()
Application.ScreenUpdating = False
With Sheets("Indigenous_Hours_Program").Range("F1", Range("F" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:=Array("6346", "6331", "4365", "6150", "2199", "2199", "4251", "6083", "6122", "2406", "2017", "2012", "2094", "2099", "2117", "5141", "5041"), Operator:=xlFilterValues
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True

End Sub

However I have been given a large report (2000+ lines), where I need to exclude (delete) employees (400+) based on employee number (we have no other defining attribute unfortunately, it's an external report).


I have tried updating the above formula with employee numbers, however I get errors on stacking and syntax ...

I hope someone can please help me!

Oh and I have no idea how to code, very basic understanding

 

[Michael M]

Do you mean any employee with the employee number greater than 400 ??

where I need to exclude (delete) employees (400+)

 

[dboatswain]

Oh I wish! I need to delete a large number of rows based on unique employee numbers ...

i.e. exclude Employee Numbers:

88899663
88212111
68721111
88823111
+ hundreds more!

 

[Michael M]

OK....but based on what criteria ??
Greater than a certain number ??
Appears more than once ??

 

[Trebor76]

Hi dboatswain,

Welcome to MrExcel!!

If you have the list of employee numbers to be deleted on a separate tab you could write a VLOOKUP formula in an unused column nested in a IFERROR function that would flag the matches with some text (say "Delete Me") and return nothing for no matches. You could then use this column to delete the matches i.e. all "Delete Me" entries.

HTH

Robert

 

[dboatswain]

Thanks Robert, I will do that