Removing URL Strings Part 2

[indi visual]

I decided to start a part 2 topic with removing url strings.


The info from the first thread was super helpful.


But I wanted to tie up some loose ended confusion of mine with this code.


Beneath is an example worksheet I threw together, please have a look it's very organized. Code is from previous thread is live and functional.


http://www.iandmyself.me/killlinksexample.xlsm


Problem:

For one reason or another I have to press the Killlinks button twice to fully rid all.


Also, the text in the two last rows ("A13:A14") should be kept (but they're deleting).

 

[Hui]

Try the subroutine below

[pre]

Sub NewKillLinks()
Application.ScreenUpdating = False

Dim c As Range
Dim MyString as String
Dim testword as String
Dim startpos as Integer
Dim endpos as Integer

Worksheets("workspace area").Activate

For Each c In Worksheets("workspace area").Range(Cells(2, 1), Cells(2, 1).End(xlDown))
MyString = c.Value

If InStr(1, MyString, "(") > 0 And InStr(1, MyString, ")") > 0 Then
startpos = InStr(1, MyString, "(")
endpos = InStr(1, MyString, ")") + 1

testword = LCase(Mid(MyString, startpos, endpos - startpos))
If testword Like "*.com)" Or _
testword Like "*.net)" Or _
testword Like "*.tk)" Or _
testword Like "(http*" Or _
testword Like "(www.*" Then
If InStr(1, testword, " ") > 0 Then
NewString = MyString
Else
NewString = Left(MyString, startpos - 1) & Right(MyString, Len(MyString) - endpos)
End If
Else
NewString = MyString
End If

End If

c.Value = NewString
Next

Range("A1").Select
Application.ScreenUpdating = True
highlightresults
End Sub

[/pre]

 

[vijaySharma]

Hui,


The code is also getting rid of extra spaces which is cool.


However the Message Box "Oh! Yeah" did not appear


as the Expected Results sheet contains the extra spaces and the two cells do not compare..


Indi,


I believe you don't want those extra spaces....


~Vijay

 

[vijaySharma]

Hui,


After going through the workbook with the previous code; I was wondering why this way was utilized for the output desired...


Somewhere the array V was getting duplicate values such as "bbe"... "ggone" which is why the result was not as expected.


But then again; problem definition and output desired is a major challenge...


Your thoughts...


~Vijay

 

[Hui]

@vijaySharma

I spent a few minutes trying to decypher the code and decided it would be quicker to re-write it from scratch

I am sure Indi can adjust for the spaces as he requires

 

[indi visual]

Thanks for this guys.


It worked perfect.


Resolved.