Select each row one by one and check for a substring in each cell of it

Try this code:

Sub CopyPaste2()
Dim WS As Worksheet
Dim rcnt As Long

rcnt = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Range("A1:E" & rcnt).Select
Selection.Copy

Set WS = Sheets.Add
ActiveSheet.Name = "Test" 'renames the sheet2 to test
ActiveSheet.Paste 'pastes the copied data

Call substringdelete
End Sub

Sub substringdelete()
Dim rcnt As Long, col As Integer

rcnt = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
col = 3

For i = 2 To rcnt
While ActiveSheet.Cells(i, col).Value <> ""
If InStr(1, ActiveSheet.Cells(i, col).Value, "good morning") > 0 Then
ActiveSheet.Rows(i & ":" & i).Delete
End If
col = col + 1
Wend
col = 3
Next
End Sub

Originally Posted by jraj1106

Try this code:

Hi, when I run this code It shows me a type mismatch, is it because of the space condition that we are checking for?

Also another thing, how do I check for multiple conditions in this itself, I mean to say that If in addition to "good morning" I want other phrases as well, say for example "good day" or "good night" then also I need to delete that row.