DELETE IF CELL DOES NOT CONTAIN

[cart0250]

How would I modify the below to delete the rows in which the cells do not include 1? So if the cells contains "1,2,3" or "1" or "1,2" etc, I don't want the row deleted.

       With wsNew
            lastRow = Cells(Rows.Count, 1).End(xlUp).Row
                For i = lastRow To 2 Step -1
                     If .Cells(i, 5).Value <> "1" Then
                        Rows(i).Delete Shift:=xlUp
                     End If
                Next i
        End With

 

[VoG]

Try

With wsNew
    lastrow = .Cells(Rows.Count, 5).End(xlUp).Row
        For i = lastrow To 2 Step -1
            If Not .Cells(i, 5).Value Like "*1*" Then
                .Rows(i).Delete Shift:=xlUp
                End If
        Next i
End With
End Sub

 

[cart0250]

thanks

 

[cart0250]

Actually, after further testing, this doesn't work as I wanted. The like operator in the above code does not delete rows containing numbers other than 1, so it doesn't delete rows containing 2 or 3 or 4 etc. Any other ideas?

 

[VoG]

I tested the code in Excel 2007 with the following results:

Sheet1

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; ">Before</td><td style="font-weight:bold; ">After</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">123</td><td style="text-align:right; ">123</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">321</td><td style="text-align:right; ">321</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">212</td><td style="text-align:right; ">212</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">432</td><td style="text-align:right; ">66771</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">876</td><td style="text-align:right; ">1,2,3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">66771</td><td >ttt1x</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">1,2,3</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">4.5,8</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >ttt1x</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >ttt4x</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">2</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">3</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">1</td><td > </td></tr></table>
Excel tables to the web - Excel Jeanie Html 4

 

[cart0250]

There was a problem in my code outside of this snippet. Your solution works fine now... thanks for the help.