Skip to main content
Improved the description of the changes in the code, with the purpose of benefitting other users
Source Link

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

Just wanted to let you guys know that, after combining both M.Doerner and PeterT's answers, my code now looks as follows. It is actually bigger than before, but it surely is faster (and hopefully more organized). Thanks again for the feedback. Here is the 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

With a combination of both M.Doerner and PeterT's answers, this is how the code looks now. It is actually bigger than before, but it surely is faster (and hopefully more organized). The code is separated in a public Sub with only the configuration parameters and a call to a private Sub containing the actual code. The 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
Post Made Community Wiki by Victor Moraes
Source Link

Just wanted to let you guys know that, after combining both M.Doerner and PeterT's answers, my code now looks as follows. It is actually bigger than before, but it surely is faster (and hopefully more organized). Thanks again for the feedback. Here is the 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