Optimization

[petewilson44]

I have a function (below) that loops through a table of comments and strips out punctuation. Is there any way of simplifying the repeated lines?

Public Sub Punctuation_Strip()
'Declare & set variables
Dim x%, y%, comment$, c$
x = 2: y = 3: comment = Cells(x, y).Value
'Strip punctuation and loop
While comment <> Empty
comment = Replace(comment, ".", "")
comment = Replace(comment, ",", "")
comment = Replace(comment, "/", "")
comment = Replace(comment, "!", "")
comment = Replace(comment, "?", "")
comment = Replace(comment, "£", "")
comment = Replace(comment, "$", "")
comment = Replace(comment, "%", "")
comment = Replace(comment, "&", "")
comment = Replace(comment, "*", "")
comment = Replace(comment, "(", "")
comment = Replace(comment, ")", "")
comment = Replace(comment, "-", "")
comment = Replace(comment, "_", "")
comment = Replace(comment, "=", "")
comment = Replace(comment, "+", "")
comment = Replace(comment, "@", "")
comment = Replace(comment, "#", "")
comment = Replace(comment, ";", "")
comment = Replace(comment, ":", "")
'Replace old comment with stripped comment
Cells(x, y).Value = comment
'Move to next record
x = x + 1: comment = Cells(x, y).Value
Wend
End Sub

Many thanks

[Boytony]

Guessing without trying any coding couldn't you use the "select case" method and use case = to ascII code values. I think that way you would reduce 20 comment = lines of the code to 3 lines....

Roger

[petewilson44]

I'm not sure I see how a select case method would reduce the number of lines...would it not be exactly the same?

Would you be able to provide an example of what you mean?

[Boytony]

Sorry to get your hopes up... now I try coding it I am talking rubbish.

Will keep thinking about it....

What I had was

Select Case Comment
Case Asc(".") To Asc("/"), Asc(":") To Asc("@")
Comment = ""
End Select

Do not know if that will nudge any thing in your head to help

as I say will keep at it here

[petewilson44]

Hah, well I certainly appreciate the enthusiasm!

[MarkWalsh]

Function test()
    Dim testString As String
    testString = "Test: this%$@%$@$@$@$@$"
    Debug.Print stripIllegalCharacters(testString)
End Function


Function stripIllegalCharacters(ByVal strText As String) As String
    Dim lLoop As Long
    Dim lCount As Long
    Dim c As String
    Dim illegalCharacters As String
    
    ' Change this to a string containing the characters you want to remove
    illegalCharacters = ":!@#$%^&*("
    lCount = Len(illegalCharacters)
     
    For lLoop = 0 To lCount - 1
        c = Mid(illegalCharacters, lLoop + 1, 1)
        strText = Replace(strText, c, "")
    Next lLoop
     stripIllegalCharacters = strText
End Function

[dlary9890]

Well those are good and will certainly work but this might be a quicker way.

1. Select the cells that you want to potentially replace data in. Or the entire column for that matter.
2. Then you'll need one of these for each special character you want to eliminate.

Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

This will be much faster because this code will replace the special characters in the entire column. You don't need a loop in this code.

[petewilson44]

Thanks for the advice guys!

It runs pretty quick as it is, even thorugh 10k rows of comments

I was more interested in getting rid of repeated lines...basically just me being anal and fussy!

Thanks again