VBA - Find and Replace - is there a better way to do this?

[bbalch]

I have pieced together different bits of code over the past few years to format a P&L report being generated from an accounting system. The vba is starting to run slowly and I'm trying to clean it up to make it run a faster.

The report being formatted has 100 tabs and the code formats each tab exactly the same. I'm interested to see if there is a more efficient way to find and replace text in column A. The code loops through each sheet, selects Column A, and finds and replaces text within column A. In total there are 75+ items that are replaced.

- I've read that using "Select" is not the best practice - is there a better way to find a replace?
- This currently loops through each sheet in the workbook - is there a way to find and replace in Column A for the entire workbook at once?

For Each Sh In Worksheets        Sh.Activate
            
            Columns("A:A").Select
            Selection.Replace What:="[OperatingIncome] ", Replacement:=""
            Selection.Replace What:="_OtherIncome]", Replacement:=""
            Selection.Replace What:="_TicketIncome]", Replacement:=""
            
    Next Sh

Thanks in advance for any suggestions.

 

[steve the fish]

This doesnt require the selection of the sheet or the range in that sheet:

For Each sh In ThisWorkbook.Worksheets
    With sh.Columns("A")
        .Replace What:="[OperatingIncome] ", Replacement:=""
        .Replace What:="_OtherIncome]", Replacement:=""
        .Replace What:="_TicketIncome]", Replacement:=""
    End With
Next sh

 

[offthelip]

I think you will find it faster to load the column A into a variant array and do all the changes in memory. Try this code:

Dim sh As Worksheet
Dim tempstr As String


For Each sh In Worksheets
sh.Activate


lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
For i = 1 To lastrow
  tempstr = inarr(i, 1)
  tempstr = Replace(tempstr, "[OperatingIncome]", "")
  tempstr = Replace(tempstr, "_OtherIncome]", "")
  tempstr = Replace(tempstr, "_TicketIncome]", "")
  inarr(i, 1) = tempstr
Next i


Range(Cells(1, 1), Cells(lastrow, 1)) = inarr


Next sh

Note I found you had a space after [OperatingIncome] which caused me to have a problem is that deliberate!!