Macro to delete certain rows not containing one of 3 values

[chriscarr]

Hi guys

I was wondering if there is a macro which will delete a row if a cell in a certain column doesn;t contain one of three values. For example the criteria is in coloum L and the row needs to be left on the sheet if it contains 20000, 40000 or 60000.

I've seen macros to delete rows if it doesn't contain one value but is it possible to base it on 3?

Thanks in advance for your help.

Chris

 

[mirabeau]

maybe something like this

Sub rowsdelete()
Dim lr As Long, e, i As Long
lr = Range("L:L")(Rows.Count).End(3).Row
For i = lr To 1 Step -1
    e = Range("L:L")(i).Value
    If (e <> 20000) * (e <> 40000) * (e <> 60000) Then Rows(i).EntireRow.Delete
Next i
End Sub

 

[VoG]

Perhaps

Sub DelRows()
Dim LR As Long, i As Long
LR = Range("L" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If IsError(Application.Match(Range("A" & i).Value, Array(20000, 40000, 60000))) Then Rows(i).Delete
End If
End Sub

 

[mirabeau]

er... Peter

Sub DelRows()
Dim LR As Long, i As Long
LR = Range("L" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If IsError(Application.Match(Range("A" & i).Value, Array(20000, 40000, 60000))) Then Rows(i).Delete
End If
End Sub

etc.

 

[VoG]

er... Peter

Sub DelRows()
Dim LR As Long, i As Long
LR = Range("L" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If IsError(Application.Match(Range("A" & i).Value, Array(20000, 40000, 60000))) Then Rows(i).Delete
End If
End Sub

etc.

Ooh, Err

Sub DelRows()
Dim LR As Long, i As Long
LR = Range("L" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If IsError(Application.Match(Range("L" & i).Value, Array(20000, 40000, 60000))) Then Rows(i).Delete
Next i
End Sub