VBA to remove special characters before saving workbook

Question

I found this function to remove special characters.

Function Remove_Characters(s As String) As String
Static RegEx As Object
    If RegEx Is Nothing Then
        Set RegEx = CreateObject("VBScript.RegExp")
        With RegEx
        .Global = True
        .Pattern = "\\|/|:|\*|""|\?|<|>\|| "
        End With
    End If
Remove_Characters = RegEx.Replace(s, "")
End Function

What I want to know is how to use this to automatically remove all special characters from certain cells range (B47:L47,B51:L148) before saving the workbook?

Loop through the collection of cells in specific range and call above function inside every step of the loop. — Maciej Los, Jun 16 '15 at 19:44

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

You would want to use the Workbook_BeforeSave event. This code will run whenever someone pushes save. The next step is to loop through your ranges and run your function.

Similar answer can be found: Excel VBA - Run macro before save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ 
        Cancel as Boolean) 
    a = MsgBox("Do you really want to save the workbook?", vbYesNo) 
    If a = vbNo Then Cancel = True 
End Sub

edited May 23 '17 at 11:51

Community

1
1

answered Jun 16 '15 at 19:45

Holmes IV

1,673
2
23
47

Thanks for the trigger. – Rayearth Jun 16 '15 at 20:21

Maciej Los · Accepted Answer · 2015-06-16T22:08:14.293

0

Please, read my comment to the question. This should help you:

Dim rng As Range, c As Range
Set rng = ThisWorkbook.Worksheets("Arkusz1").Range("B47:L47,B51:L148")
For Each c In rng.Cells
    c.Value = Remove_Characters(c.Value)
Next c

edited Jun 16 '15 at 22:08

answered Jun 16 '15 at 19:45

Maciej Los

8,468
1
20
35

Works if the range is a single cell address but if I put ("B47:L47,B51:L148") it returns run-time error '1004': Method 'Range' of object '_Global' failed. What's wrong with it? – Rayearth Jun 16 '15 at 20:17
Ohhhh, i forgot to add that you have to specify range as `ThisWorkbook.Worksheets("WshName").Range("Address")`. If this won't help, please let me know. – Maciej Los Jun 16 '15 at 20:25
`For Each c In ThisWorkbook.Worksheets("Arkusz1").Range("B47:L47,B51:L148")` returns run-time error '9': subscript out of range. Is that the correct syntax? – Rayearth Jun 16 '15 at 21:52
Please, see updated answer ;) I tested it and does not get any error. – Maciej Los Jun 16 '15 at 22:08
I call this sub in ThisWorkbook with `Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel as Boolean)` I don't get any errors when the workbook is saved from Excel, but when I save file from MS Visual Basic editor it still gives me "Run-time error '9': subscript out of range" error. – Rayearth Jun 17 '15 at 19:06
I will do some more testing. Anyway, thank You for all the help! – Rayearth Jun 17 '15 at 19:31
You're very welcome. I keep fingers crossed for you ;) – Maciej Los Jun 17 '15 at 19:34

VBA to remove special characters before saving workbook

2 Answers2