PDA

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


santa1234
09-16-2022, 01:02 AM
"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, :( )



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/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78GftO_ iE (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78GftO_ iE)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h77HSGDH 4A (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h77HSGDH 4A)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h76fafzc EJ (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h76fafzc EJ)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h759YIjl aG (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h759YIjl aG)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h74pjGcb Eq (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h74pjGcb Eq)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg.9h5uPRbWIZl9h7165DZd jg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg.9h5uPRbWIZl9h7165DZd jg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
DocAElstein
09-17-2022, 03:39 PM
If you could attach a reduced size sample with a Before ( Input) and an After (Output), that would help us understand your requirement clearly
DocAElstein
09-27-2022, 03:23 PM
@ 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/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78GftO_ iE (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78GftO_ iE)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h77HSGDH 4A (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h77HSGDH 4A)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h76fafzc EJ (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h76fafzc EJ)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h759YIjl aG (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h759YIjl aG)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h74pjGcb Eq (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h74pjGcb Eq)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg.9h5uPRbWIZl9h7165DZd jg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg.9h5uPRbWIZl9h7165DZd jg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
santa1234
10-01-2022, 09:49 AM
Attached in post#1 a reduced size sample with a Before ( Input) and an After (Output).
DocAElstein
10-02-2022, 09:18 PM
Hello susan santa 12345

I have done a few tests and initial investigations here
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=16726&viewfull=1#post16726
https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16727&viewfull=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

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