VBA delete rows based on criteria

[Katy Jordan]

Hi, i am after a vba code that will delete row if there is a string in Col M and then delete row if there is a 0 in Col L

Excel Workbook
	L	M
4	Amount	String
5	6271	#N/A
6	0	#N/A
7	3630	#N/A
8	1320	#N/A
9	0	#N/A
10	0	#N/A
11	500	#N/A
12	1210	#N/A
13	2000	#N/A
14	0	#N/A
15	800	BUCKINGIND2
16	660	#N/A
17	715	#N/A
18	110	#N/A
19	0	#N/A
20	3920	CORDA
21	900	#N/A
22	0	#N/A
23	0	#N/A
Sheet29

 

[jeffreybrown]

How about something like this...

Please test on a test set of data before implementing.

Sub DelRows()
    Application.ScreenUpdating = False
    With Range("M4", Range("M" & Rows.Count).End(xlUp))
        .AutoFilter field:=1, Criteria1:="<>#N/A"
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    With Range("L5", Range("L" & Rows.Count).End(xlUp))
        .Replace what:="0", replacement:="", lookat:=xlWhole
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
End Sub