delete entire row if value

[sparky101747]

I need to delete the entire row if the value does not end with 000 or 500. I have varying number of rows week to week and the column (W) may or may not contain blank cells.

W
750000
600000

550250 <-----Delete entire row
350500
150500
250350 <------Delete entire row

Thank You in advance.

 

[GTO]

Using column A for example:

Excel Workbook
	A
1	750000
2	600000
3
4	550250
5	350500
6	150500
7	250350
Sheet1

Excel 2003

Try:

Option Explicit
    
Sub exa()
Dim lRow As Long, i As Long
    
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = lRow To 1 Step -1
        If Not Right(Cells(i, "A").Text, 3) Like "000" And Not Right(Cells(i, "A").Text, 3) Like "500" Then
            Rows(i).Delete xlShiftUp
        End If
    Next
    
End Sub

Hope that helps,

Mark

 

[VoG]

Try

Sub DelW()
Dim LR As Long, i As Long
LR = Range("W" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    With Range("W" & i)
        If Right(.Value, 3) <> "000" And Right(.Value, 3) <> "500" Then .EntireRow.Delete
    End With
Next i
End Sub

 

[VegasUser]

Try this

Sub DelRow()
    Columns("W:W").Select
    For Each objCell In Selection
        If (objCell.Value2 Mod 500) <> 0 Then
            objCell.EntireRow.Delete
        End If
    Next
End Sub

 

[sparky101747]

Thanks to everyone that replied, I used GTO's But added "And Not Right(Cells(i, "W").Text, 3) Like " "" to the end because it was deleting the rows with blank cells also so it looks like this now:

Option Explicit
 
Sub exa()
Dim lRow As Long, i As Long
 
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
 
    For i = lRow To 1 Step -1
        If Not Right(Cells(i, "W").Text, 3) Like "000" And Not Right(Cells(i, "W").Text, 3) Like "500" And Not Right(Cells(i, "W").Text, 3) Like "" Then
            Rows(i).Delete xlShiftUp
        End If
    Next
 
End Sub

It seems to work, Is the correct way or is there another way?