Deleting Rows if the Row Contains Specific Text

[julia55]

I am trying to figure out how to delete a whole row if the row contains the text "Run Date". However, the cell with that text will also have varying text before and after run date. I tried using the below, but it won't work since my cell doesn't contain exact text.

Sub DeleteRowWithContents()
Last = Cells(Rows.Count, "D").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "D").Value) = "Run Date" Then
Cells(i, "A").EntireRow.Delete
End If
Next i
End Sub

 

[Zack Barresse]

Hi there, welcome to the board!

Check out the InStr() function, instead of a straight comparison.

 

[julia55]

Hi there, welcome to the board!

Check out the InStr() function, instead of a straight comparison.

Thanks!
I tried looking at that function but couldn't figure out how to incorporate it into my code.

 

[julia55]

I figured out a code that worked. I used the below:

Sub DeleteRowsTOTALONLY()
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("I65536").End(xlUp))
Do
Set c = SrchRng.Find("Total Only", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub

 

[Zack Barresse]

To finish out the trail of thought...


<font face=Courier New><SPAN style="color:#00007F">If</SPAN> InStr(1, Cells(i, "D").Value, "Run Date") <> 0 <SPAN style="color:#00007F">Then</SPAN></FONT>


Also, some minor changes to your routine...


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DeleteRowsTOTALONLY()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, SrchRng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Set</SPAN> SrchRng = ActiveSheet.Range("A1:I" & Cells(Rows.Count, "I").End(xlUp).Row)<br>    <SPAN style="color:#00007F">Do</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> c = SrchRng.Find("Total Only", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> c.EntireRow.Delete<br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


It will keep your range dynamic, and the range object is declared as one as opposed to leaving it as a Variant type. Also, the Find method will remember what you used last as its settings and use those, so be sure to specify any settings you don't want as 'assumed'.

HTH