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?

Answer 1

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

Answer 2

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