in VBA if the given string is found then delete everything between two newlines where the string appears.

"Looking for help in VBA"

if the given string is found in any excel cell then delete everything between two newlines where the string appears.

---

---

---

Moderator Notice:
The OP sent me this PM. It might be related to this question? It might be an attempt to reply to some of my recent comments to him regarding his posting style.
(It continues to be difficult to communicete or get through to this guy, :( )

Quote:

---

Originally Posted by santa1234

How can I delete sentences with a specific keyword in excel cells in all sheets?

Hello Sir,
How can I delete sentences with a specific keyword in excel cells in all sheets?
I searched a lot on google but could not find a solution.
Thanks.

---

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

If you could attach a reduced size sample with a Before ( Input) and an After (Output), that would help us understand your requirement clearly

@ susan santa 12345 et al

You continue to post short badly explained questions, and either ignore or don’t understand the various things I have said to you.

My best guess is that you are either
_ a Bot,
_ a total idiot,
_ just trying your luck at getting Homework questions answered quickly
_ just trying your luck at answering someone else’s questions and have no idea or interest in them yourself
_ deliberately trying to be a pain in the arse.

I will probably delete, close , ban you, or some combination in a few days to tidy the place up a bit, unless you improve somehow

Alan




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Attached in post#1 a reduced size sample with a Before ( Input) and an After (Output).

Hello susan santa 12345

I have done a few tests and initial investigations here
https://excelfox.com/forum/showthrea...ll=1#post16726
https://excelfox.com/forum/showthrea...ll=1#post16727

( I think you may have an error in your supplied data – you have searched string as one string to search for, but your data suggests that it should be maybe searched*string )

A Solution …..
We Split that text in a cell by the line separator, to give us an array where each element contains the text of a paragraph/ line.
Check each element content for the search strings, and if no strings are found we can use that element to finally re build the complete cell content

Here is an example macro for cell A2

Code:

---

Sub CleanUpCellA2() ' https://excelfox.com/forum/showthread.php/2818-in-VBA-if-the-given-string-is-found-then-delete-everything-between-two-newlines-where-the-string-appears?p=16718&viewfull=1#post16718
Rem 0 worksheet data info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Rem 1 We Split that text in a cell by the line separator, to give us an array where each element contains the text of a paragraph/ line.
Dim Celtxt As String: Let Celtxt = Ws1.Range("A2").Value2
Dim SptTxt() As String: Let SptTxt() = Split(Celtxt, vbLf, -1, vbBinaryCompare)
Rem 2  Check each element content for the search strings, and if no strings are found we can use that element to finally re build the complete cell content
Dim Cnt As Long, NewStr As String
    For Cnt = 0 To UBound(SptTxt())
        If InStr(1, SptTxt(Cnt), "paragraph/line", vbBinaryCompare) = 0 And InStr(1, SptTxt(Cnt), "searched*string", vbBinaryCompare) = 0 And InStr(1, SptTxt(Cnt), "#VBA", vbBinaryCompare) = 0 Then
        Let NewStr = NewStr & SptTxt(Cnt) & vbLf
        Else
        End If
    Next Cnt
 Let NewStr = Left(NewStr, Len(NewStr) - 1)
Rem 3 Output
 Let Ws1.Range("A3").Value2 = NewStr
End Sub

---

Before: (A2)

Code:

---

in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in VBA"

if the given string is found in any paragraph/line excel cell then delete everything between two newlines where the string appears. in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in VBA"searched*string

if the given string is found in any excel cell then delete everything between two newlines where the string appears.

---

After: (A3)

Code:

---

in VBA if the given string is found then delete everything between two newlines where the string appears.
"Looking for help in VBA"


if the given string is found in any excel cell then delete everything between two newlines where the string appears.

---

Alan

---

VBA delete everything between two newlines where the string appears.xls https://app.box.com/s/zraunl47i9bb477ep90e104m148x7ylh