Delete Row if cell contains value

[sharky12345]

Morning all,

I need the code please that will search cells G2:G5000 on the worksheet "Letters" and will delete the entire row only if the figure '1' appears in that row, (in column G).

The code must leave intact any rows where the figure is anything other than 1. I will be looking to repeat this code to search for figures running from 1 to 30. The column itself will only have figures ranging from 1 to 30 so it is important that the code doesn't delete '11' or '21' if searching for '1' - does this make sense?

 

[VoG]

Try

Sub Sharky()
Dim LR As Long, i As Long
With Sheets("Letters")
    LR = .Range("G" & Rows.Count).End(xlUp).Row
    For i = LR To 2 Step -1
        If .Range("G" & i).Value = 1 Then .Rows(i).Delete
    Next i
End With
End Sub

 

[Peter_SSs]

An alternative approach you may wish to consider:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_Rows()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Letters").Range("G1:G5000")<br>        .AutoFilter field:=1, Criteria1:=1<br>        .Offset(1).EntireRow.Delete<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[sharky12345]

Guys - many thanks for your quick response!

Having looked at this again I wonder if I could do something else instead - how about deleting the values in the cells found but not the row so the only thing that is blank will be the cell in that column?

 

[VoG]

Try

Sub Sharky()
Dim LR As Long, i As Long
With Sheets("Letters")
    LR = .Range("G" & Rows.Count).End(xlUp).Row
    For i = LR To 2 Step -1
        If .Range("G" & i).Value = 1 Then .Range("G" & i).ClearContents
    Next i
End With
End Sub

 

[sharky12345]

Peter - what can I say, that works great - thank you!

I'll be back with more little problems in due course!

 

[Peter_SSs]

Again, you could do them all at once:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_1s()<br>    Sheets("Letters").Range("G2:G5000").Replace What:=1, Replacement:="", LookAt:=xlWhole, SearchFormat:=False, ReplaceFormat:=False<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[sharky12345]

Thanks Peter!