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
Originally Posted by santa1234
Reply With Quote
Bookmarks