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

Hello,

I wanted to check for a substring (say "Good morning") in every cell of each row starting from a particular column (say C till E) one by one and, at the first instance of finding that substring in any cell it should remove that row completely and proceed to the next row and repeat the same thing till all the rows are exhausted.

I hope I did not confuse it.
Thank you for the help.

Hello..
Upload sample file

Please find the file. I want to check from the column Age, Comments and Summary and the follow the above mentioed proceedure. I would need the rows with Name A, g, q to be removed (as they contain the required substring) from the sheet. (Sorry Iam not able to attach the file, facing some problem)

Name Reg. Date Age Comments Summary
A 22-04-2012 1 alkdfhklashf lkgjlkj good morningladsjflkj
b 22-04-2012 2 qewrthdkj xkcnj,xzn
c 22-04-2012 3 gkjqhkrtghkgv vkhakh
d 22-04-2012 4 dskbvakjdhv cxbv
e 22-04-2012 5 dslfhekrhg ewru
f 22-04-2012 6 dvnbcxmvh xzjcb
g 22-04-2012 7 lkdgalsjgood morningadlfjas aieur
h 22-04-2012 8 aldshgfkladh zxc,mnx
i 22-04-2012 9 adterig rietuire
j 22-04-2012 34 dlktjeri dfgghcvxc
k 22-04-2012 23 cxvn,mn xcbcvn
l 22-04-2012 12 eritue dfgdfh
m 22-04-2012 76 cxbv sdfsdf
n 22-04-2012 87 aeiwtu xcvbcvbn
o 22-04-2012 53 adncb sfsfgdh
p 22-04-2012 33 akeur xcvbcvbn
q 22-04-2012 54 dmnfv,good morningadlkfjaklj sddfd
r 22-04-2012 24 dlkfn zxvzv
s 22-04-2012 65 arot dfhdfh
t 22-04-2012 43 damvn cxvdfghdf

It would have been better if you could upload the sample data in execl itself...

Run the macro and check...

Test file.xlsm


Regards..

But you didn't specify abt copying data frm sheet1 to sheet2 in the previous post.. Pls specify what has to be copied ie. based on what criteria should the copying be done..

Originally Posted by jraj1106

But you didn't specify abt copying data frm sheet1 to sheet2 in the previous post.. Pls specify what has to be copied ie. based on what criteria should the copying be done..

True, I just wanted to do a straight forward copy from sheet1 to another sheet (sheet2) which I was able to, and then the above procedure to be implemented in that sheet (sheet2).

This is exactly what I intend to do

'Creating a New Sheet and copying the data in that sheet from

Sub CopyPaste2()
With Worksheets("Sheet1")
ActiveSheet.Select
Selection.Copy
End With

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

End Sub

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

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

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

I know just a copy paste without a condition is redundant, but this is something that I wanted, please let me know the issue as Iam just able to copy with this code but it isnt going to the next subroutine (Iam just a day old beginner, please ignore silly mistakes)

In short I need the 2 subroutines to run sequentially.

Thanks
Abhushan

Hi Abhushan86,

Welcome to the forum!!

As well as jraj1106's clever solution here's my attempt (which may also help anyone who is unable to download from the Internet):

Option Explicit
Sub Macro1()

    'http://www.excelforum.com/excel-programming/827684-select-each-row-one-by-one-and-check-for-a-substring-in-each-cell-of-it.html
    
    Dim lngLastRow As Long, _
        lngMyRow As Long
    Dim strMyText As String
    Dim strMyCols As String, _
        strMyColFrom As String, _
        strMyColTo As String
    Dim varMyCol As Variant
    
    Application.ScreenUpdating = False
    
    strMyCols = "D:E" 'Columns to be checked for 'strMyText'. Change to suit.
    strMyText = "Good morning" 'Text to be search in 'strMyCols'. Change to suit.
    
    lngLastRow = Range(strMyCols).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For Each varMyCol In Split(strMyCols, ":")
        If strMyColFrom = "" Then
            strMyColFrom = varMyCol
        Else
            strMyColTo = varMyCol
        End If
    Next varMyCol
    
    For lngMyRow = lngLastRow To 2 Step -1
        If Evaluate("COUNTIF(" & strMyColFrom & lngMyRow & ":" & strMyColTo & lngMyRow & ",""*" & strMyText & "*"")") > 0 Then
            Rows(lngMyRow).EntireRow.Delete
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True
    
    MsgBox "Any applicable rows have now been deleted."
    
End Sub

HTH

Robert

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.

For me th code works perfect. There's no error..

yes... You can use if else statement for checking multiple conditions...