CODEWITHSUNDEEP
arraysexcelvbareplacecell
Rose
Rose

Reputation: 203

Speed Up Characters replacement VBA

I have this little code that replaces the letters from a table like this (find the left string and replace it with the right string):

enter image description here

However it takes a great amount of time to do all the replacements in the sheets I have (just 2). Nearly 10 seconds. Is there a way to speed this up pls? Many thanks for taking the time!!

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
Dim ReplaceCount As Long

Set sht = Sheets("Sheet1")
Application.ScreenUpdating = False

'Create variable to point to your table
Set tbl = Worksheets("Sheet1").ListObjects("StringReplace")

'Create an Array out of the Table's Data
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)
  
'Designate Columns for Find/Replace data
fndList = 1
rplcList = 2

'Loop through each item in Array lists
For x = LBound(myArray, 1) To UBound(myArray, 2)

'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
    For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> tbl.Parent.Name Then
            sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
            
        End If
    Next sht
Next x
Application.ScreenUpdating = True

Upvotes: 1

Views: 252

Answers (1)

VBasic2008
VBasic2008

Reputation: 55028

Replace Strings in Multiple Worksheets

The Code

Option Explicit

Sub replaceOddStrings()
    
    Const WorksheetName As String = "Sheet1"
    Const TableName As String = "StringReplace"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim Data As Variant: Data = wb.Worksheets(WorksheetName) _
        .ListObjects(TableName).DataBodyRange.Value
    
    Dim ws As Worksheet
    Dim i As Long
    
    Application.ScreenUpdating = False
    For Each ws In wb.Worksheets
        If ws.Name <> WorksheetName Then
            For i = 1 To UBound(Data, 1)
                ws.UsedRange.Replace Data(i, 1), Data(i, 2), xlPart, , False, _
                    False, False, False
            Next i
        End If
    Next ws
    Application.ScreenUpdating = True

    MsgBox "Strings replaced.", vbInformation, "Success"
 
End Sub

Upvotes: 1

Related Questions