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

[Abhushan86]

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.

[jraj1106]

Hello..
Upload sample file

[Abhushan86]

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

[jraj1106]

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

[Abhushan86]

please find the file, sorry for the inconvenience.

[jraj1106]

Run the macro and check...

Test file.xlsm


Regards..

[Trebor76]

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

Please Login or Register  to view this content.

HTH

Robert

[Abhushan86]

Run the macro and check...

Attachment 152742


Regards..

Hey Thanks, \m/

The code runs fine, but I had another query, what If I had to use two subroutines, first one for copying from sheet1 to sheet2 and then checking for multiple substrings in the sheet2 (as above) , how would that be done?

Thanks,
Abhushan.

[Abhushan86]

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

Please Login or Register  to view this content.

HTH

Robert

Thanks for the help Robert. \m/

[Rick Rothstein]

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).

Give this code a try...

Please Login or Register  to view this content.

[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..

[Abhushan86]

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

[jraj1106]

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

[Abhushan86]

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.

[jraj1106]

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

[Abhushan86]

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

Well, it isnt working as my data consists of a lot many other entries which are numeric, alphanumeric and all mixed together, I cant share the excel as it is confidential. Anyways If you get a hint about why this Type Mismatch is arising, then please do let me know.

Could you please let me know if I could check multiple conditions i.e., in addition to "good morning" I want to check for 3-4 more conditions, how could I do that? Please help..

[Abhushan86]

Could you please let me know if I could check multiple conditions i.e., in addition to "good morning" I want to check for 3-4 more conditions, how could I do that? Please help..

Please help....

[jraj1106]

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