Find and Replace

Question

I had written a macro that was working fine but it looks like there was some sort of update and now my code isn't working. Can anyone help me identify what's going wrong or why this function would no longer work?

Here is the function as it is:

Function FindReplace(CellValue$)
    Dim strPattern$: strPattern = "[^A-Za-z, ]+"    'Pattern to only take care of letters
    Dim strReplace$: strReplace = ""    'Replace everything else with blank
    Dim regex As Object
    Set regex = CreateObject("vbscript.regexp")

    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    FindReplace = regex.Replace(CellValue, strReplace) 'RegEx Function replaces the pattern with blank
End Function

I'm trying to have it look at a a cell and only allow specific characters to surface.

Here is the larger code that this function is a part of:

'Concatenate all the data in the rows into columns A
    Sheets("Formula2").Select
    Dim Lastrow%: Lastrow = ActiveSheet.UsedRange.Rows.Count
    Dim strConcatenate$, I%, j%
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = ActiveSheet

    Range("A:A").Clear

    For I = 1 To Lastrow
        For j = 2 To lastColumn(I) 'Calls function "LastColumn" to get the last column of each row
            strConcatenate = strConcatenate & FindReplace(ws.Cells(I, j))
        Next j
        ws.Cells(I, 1) = strConcatenate 'This will past the finished string into column [A] in the specific row
        strConcatenate = "" 'blanks the string, so the next string in the next row is fresh
    Next I

Answer 1

I suggest the following code (tested with A "", B "Test", C "123").

Note that you need to adjust the For j = 2 To 3 loop to end at your last column.

Option Explicit

Sub Test()
    'Concatenate all the data in the rows into columns A
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Formula2") 'avoid select and specify the sheet by name

    Dim LastRow As Long
    LastRow = ws.UsedRange.Rows.Count

    Dim strConcatenate As String
    Dim i As Long, j As Long

    ws.Range("A:A").Clear 'always specify in which sheet a range is!

    For i = 1 To LastRow
        For j = 2 To 3 'changed that for testing to 3
            strConcatenate = strConcatenate & FindReplace(ws.Cells(i, j).Value)
        Next j
        ws.Cells(i, 1).Value = strConcatenate 
        strConcatenate = "" 
    Next i
End Sub

Function FindReplace(CellValue As String) As String
    Dim strPattern As String
    strPattern = "[^A-Za-z, ]+"    'Pattern to only take care of letters

    Dim strReplace As String
    strReplace = ""    'Replace everything else with blank

    Dim regex As Object
    Set regex = CreateObject("vbscript.regexp")

    With regex
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    FindReplace = regex.Replace(CellValue, strReplace) 'RegEx Function replaces the pattern with blank
End Function