Delete Row if cell contains value

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Try

Code:
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

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
Peter - what can I say, that works great - thank you!

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top