Slow loop processing

nyconfidential

New Member
Joined
Jul 22, 2015
Messages
49
Office Version
  1. 365
  2. 2016
I was asked to find and replace pound signs with a percentage sign in a particular workbook. Rather than doing find and replace all, I want to loop through all worksheets in VBA and see if the first character in each cell is a pound sign, remove that pound sign and replace it with a percentage sign(I just want to replace a pound sign if it is the FIRST character in the cell string).

The code below accomplishes my goal, but there are 65 worksheets in this workbook, and some of the worksheets have pound signs in over 5000 cells, so it takes almost 10 minutes to complete. Can anyone tell me if there is a way to optimize my code below? Is looping through each cell in the range the smartest way to do this?(Apologies, I come from an Access background and am accustomed to looping through recordsets when updating values). Appreciate any input.

Code:
Public Sub ReplaceWorksheetValues()
On Error Resume Next

Dim WrkSht As Worksheet
Dim rCell As Range
Dim rRng As Range


For Each WrkSht In ActiveWorkbook.Worksheets
    Range("A1").Select
    Application.StatusBar = "Updating " & WrkSht.Name & ", please wait..."
    Set rRng = WrkSht.UsedRange
    For Each rCell In rRng.Cells
        'Debug.Print rCell.Address, rCell.Value
        'Find cell values that start with a pound sign
        If Left(rCell.Value, 1) = "#" Then
            'Strip the first character(pound sign), prepend an equals sign to the start of the string
            rCell.Value = "%" & Right(rCell.Value, Len(rCell.Value) - 1)
        End If
    Next rCell
    
    Application.StatusBar = "Updating " & Str(WrkSht.Name)
    
Next WrkSht
    
    
End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top