Just wanted to let you guys know that, after combiningWith a combination of both M.Doerner and PeterT's answers, mythis is how the code now looks as followsnow. It is actually bigger than before, but it surely is faster (and hopefully more organized). Thanks again forThe code is separated in a public Sub with only the feedbackconfiguration parameters and a call to a private Sub containing the actual code. HereThe Array of forbidden strings is replaced with a RegExp pattern and the logic to work directly with Ranges is changed to work with an Array.
This is the working code:
Option Explicit
Sub RemoveAllForbiddenCharacters()
Const FIRST_ROW = 2
Const TARGET_COLUMN = "J"
Const REGEX_PATTERN = "[\\/:%'*?<>|"".]"
Const REGEX_REPLACE = ""
Call RemoveCharactersWithRegExp(FIRST_ROW, TARGET_COLUMN, REGEX_PATTERN, REGEX_REPLACE)
End Sub
Private Sub RemoveCharactersWithRegExp(lngFirstRow As Long, strColumn As String, strPattern As String, strReplace As String)
Dim regEx As New RegExp
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
Dim ws As Worksheet
Dim lngLastRow As Long
Dim rngCells As Range
Dim dataValues As Variant
For Each ws In ThisWorkbook.Worksheets
lngLastRow = ws.Cells(ws.Rows.Count, strColumn).End(xlUp).Row
Set rngCells = ws.Range(strColumn & lngFirstRow).Resize(lngLastRow - lngFirstRow + 1, 1)
dataValues = rngCells.Value2
Dim i As Long
For i = LBound(dataValues) To UBound(dataValues)
dataValues(i, 1) = regEx.Replace(dataValues(i, 1), strReplace)
Next
rngCells.Value2 = dataValues
Next
End Sub