-
Apr 18th, 2012, 05:59 AM
#1
New Member
Optimization
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules