Macro to Find and Replace - Speed

[shades]

I had written a macro about two years ago that replaced longer company names with common abbreviations. I store it in Personal.xls, and it has been relatively fast (2-5 sec for most projects with < 2000 rows). However, I tried it on a file with 15,000 rows, and it choked Excel - i.e. it wouldn't run and had to be force quite (Excel XP on Windows 2000).

Here is a portion of the macro (I have about 40 companies that need to be abbreviated, names here have been simplified to protect the guilty ). I stepped through a couple of these lines to see what would happen, and each line took at least 1-2 min.

Sub ChgCompNames()

Application.ScreenUpdating = False
    Cells.Replace What:="Company AAAA", Replacement:="AAAA", LookAt:=xlPart, SearchOrder:=xlByRows
    Cells.Replace What:="Company BBBB", Replacement:="BBBB", LookAt:=xlPart, SearchOrder:=xlByRows
    Cells.Replace What:="Company CCCC", Replacement:="CCCC", LookAt:=xlPart, SearchOrder:=xlByRows
    Cells.Replace What:="Company DDDD", Replacement:="DDDD", LookAt:=xlPart, SearchOrder:=xlByRows
.
'etc. for all company names
.
.
Application.ScreenUpdating = True
End Sub

Is there a better way to go about the replacing? Or is there a way to make this more efficient? (I use this macro just about everyday, and this is the first time that I have run into such a problem.)

 

[Legacy 21301]

I did a quick test in a range of 300,000 cells and there was only 1 second difference...

10X30000 = 4 secs (all 10 columns)
1X30000 = 3 secs (1 column)

But perhaps over 40 finds then it adds up, but shades may not be using that much data so it may not make much difference. No harm anyway.

I just tested 1 col X 30000 and 10 cols X 30000 (run each one 6 times), looking for one value to replace.

The average difference in run times between the 2 sets of data was 0.173 seconds - as you say, it would need a lot of different values to find/replace before the difference became significant.
But then it's just as easy to restrict the columns as not.

 

[Tommy Bak]

Hi
Another approach could by using the autofilter. This works very fast too.

Sub TryMe()
Dim IndexCol As Range
Dim LookArr(), ReplArr()
Dim x As Long
Set IndexCol = Application.InputBox(prompt:="Point out the header in the column for replacement", Type:=8)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LookArr = Array("Company AAAA", "Company BBBB", "Company CCCC", "Company DDDD", "Company EEEE")
ReplArr = Array("AAAA", "BBBB", "CCCC", "DDDD", "EEEE")

For x = LBound(LookArr) To UBound(LookArr)
    IndexCol.AutoFilter Field:=IndexCol.Column, Criteria1:=LookArr(x)
'Edited   
 Range(IndexCol.Offset(1, 0), Cells(65536, IndexCol.Column).End(xlUp)) = ReplArr(x)
Next

IndexCol.AutoFilter
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

regards
Tommy Bak[/quote]

 

[Legacy 21301]

Hi
Another approach could by using the autofilter. This works very fast too.

Sub TryMe()
Dim IndexCol As Range
Dim LookArr(), ReplArr()
Dim x As Long
Set IndexCol = Application.InputBox(prompt:="Point out the header in the column for replacement", Type:=8)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LookArr = Array("Company AAAA", "Company BBBB", "Company CCCC", "Company DDDD", "Company EEEE")
ReplArr = Array("AAAA", "BBBB", "CCCC", "DDDD", "EEEE")

For x = LBound(LookArr) To UBound(LookArr)
    IndexCol.AutoFilter Field:=IndexCol.Column, Criteria1:=LookArr(x)
'Edited   
 Range(IndexCol.Offset(1, 0), Cells(65536, IndexCol.Column).End(xlUp)) = ReplArr(x)
Next

IndexCol.AutoFilter
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

regards
Tommy Bak

[/quote]

But is it faster than replace/find - particularly as their might be more than 1 column involved.
And if faster, how much faster?

 

[Tommy Bak]

Hi ponsy
As I understood the problem, only one column was involved.
If that's right, autofilter is faster, about 5-6 times as fast, if I'm using 30000 / 60000 rows of data.
But I guess that it depends on the number of companies / rows of data.

I had to change it a little so i wont alterate the headers.

Sub AutoFilterModel()
Dim IndexCol As Range
Dim LookArr(), ReplArr()
Dim x As Long
Dim LastRow As Long
'Dim start As Long
Set IndexCol = Application.InputBox(prompt:="Point out the header in the column for replacement", Type:=8)
'start = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LookArr = Array("Company AAA", "Company BBB", "Company CCC", "Company DDD", "Company EEE", "Company FFF", "Company GGG", "Company HHH", "Company III")
ReplArr = Array("AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG", "HHH", "III")

For x = LBound(LookArr) To UBound(LookArr)
    IndexCol.AutoFilter Field:=IndexCol.Column, Criteria1:=LookArr(x)
    LastRow = Cells(65536, IndexCol.Column).End(xlUp).Row
    If LastRow > 1 Then _
        Range(IndexCol.Offset(1, 0), Cells(LastRow, IndexCol.Column)) = ReplArr(x)
Next

IndexCol.AutoFilter
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'MsgBox Timer - start
End Sub

regards
Tommy Bak

 

[Legacy 21301]

Hi ponsy
As I understood the problem, only one column was involved.
If that's right, autofilter is faster, about 5-6 times as fast, if I'm using 30000 / 60000 rows of data.
But I guess that it depends on the number of companies / rows of data.

Actually the poster said "Usually the company will be in one of these columns: A, B, C, or D (i.e. if in A, then it won't be in any other column)".
I'm not sure whether he meant that all the company names will be in one column only, or whether no more than one name will appear in each row in either A or B or D or E.

In any event my comments were merely intended to suggest that the most efficient method is likely to depend upon the make up of the data.
There are a lot of possible variables in the set-up that could affect the comparative run-times - not least of all being the number of different values(as you point out) nor the number of cells to be replaced.

 

[shades]

Hi ponsy
As I understood the problem, only one column was involved.
If that's right, autofilter is faster, about 5-6 times as fast, if I'm using 30000 / 60000 rows of data.
But I guess that it depends on the number of companies / rows of data.

Actually the poster said "Usually the company will be in one of these columns: A, B, C, or D (i.e. if in A, then it won't be in any other column)".
I'm not sure whether he meant that all the company names will be in one column only, or whether no more than one name will appear in each row in either A or B or D or E.

In any event my comments were merely intended to suggest that the most efficient method is likely to depend upon the make up of the data.
There are a lot of possible variables in the set-up that could affect the comparative run-times - not least of all being the number of different values(as you point out) nor the number of cells to be replaced.

In a specific spreadsheet all companies will be in the same column, and there could be as many as 1200 occurrences for each company in that column. And the number of company names to change is about 40.

(BTW I'm not at work, just happened to catch a few minutes to check in. Really appreciate the suggestions and I look forward to next week to examine the implications and possibilities of each.)

 

[parry]

Hi ponsy
As I understood the problem, only one column was involved.
If that's right, autofilter is faster, about 5-6 times as fast, if I'm using 30000 / 60000 rows of data.

Hi Tommy, Im interested in what your saying here. If I understand what your doing is that your placing the lookfor and replace values into arrays as I had done in my code but instead of using the replace method you are filtering the target column multiple times (once for each company) and placing the array value directly into the visible range each time.

My understanding is that the replace function is looking at every cell in the range (why I reduced range to 1 column) then performing a replace as it finds it where by filtering you dont need to look at every cell and are replacing the values in one hit as it were. Filtering in itself is an action but I suppose thats not much overhead.

I wonder if your getting quicker results more because you are turning off the calculation which is a bit of a cheat in my opinion.
Why I say this is that after the macro actions have taken place your turning calcs back to automatic but this in itself doesnt result (I may be wrong) in Excel actually performing the calc so the calc is actually done the next time the user performs an action so you are reducing the time by this amount.

Also, I have never been able to successfully get a timer to work as I would like in Excel. Ive done quite a few array operations and other methods and the calculation time at the end is often not taken into consideration. For example, a message box or a value placed in a cell happens before Excel has finished calculating, even when this line is definelty the last line in the procedure.

Please dont take any of this as a criticism. Im just curious about the speeds of different operations.

 

[Tommy Bak]

Hi Parry
You have understood what I'm doing correctl

I wonder if your getting quicker results more because you are turning off the calculation which is a bit of a cheat in my opinion.

I did test under same conditions (screenupdating and calculation).
I even changed the seach and replace macro a little so it didn't use a full column but only actual range.

What I found out was not unexpected:
Given a big datarange and few searchitems autofilter is much quicker
Given a smal datarange (3000 rows) and few seachitems (10) autofilter is still fastest but not much (2 times)
Given a big datarange (40000 rows) and many seachitems (40) autofilter is still fastest but not much (2 times)
Given a small datarange and many seachitem seach and replace is the fastest and get better with fewer item and more searchitems

That fits well with your idea about replacing in chunks. If the chuncks are big autofilter has an advantage,
and if the chunks are small search and replace is the winner.

the actual macroes used was

Sub AutoFilterModel()
Dim IndexCol As Range
Dim LookArr, ReplArr
Dim x As Long
Dim lastrow As Long
Dim start As Long
Set IndexCol = Application.InputBox(prompt:="Point out the header in the column for replacement", Type:=8)
start = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LookArr = Workbooks("Person.xls").Sheets("Data").Range("A2:B41")

For x = LBound(LookArr, 1) To UBound(LookArr, 1)
    IndexCol.AutoFilter Field:=IndexCol.Column, Criteria1:=LookArr(x, 1)
    lastrow = Cells(65536, IndexCol.Column).End(xlUp).Row
    If lastrow > 1 Then _
        Range(IndexCol.Offset(1, 0), Cells(lastrow, IndexCol.Column)) = LookArr(x, 2)
Next

IndexCol.AutoFilter
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox Timer - start
End Sub

Sub ChgCompNames3()
Dim Rng As Range, LookArr, a As Integer, lastrow As Long
Dim start As Long
Dim test As Range

Set Rng = Application.InputBox(prompt:="Select the column that contains the company name", _
Title:="Select Column", Type:=8)
start = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Rng Is Nothing Then Exit Sub
lastrow = Cells(65536, Rng.Column).End(xlUp).Row
LookArr = Workbooks("Person.xls").Sheets("Data").Range("A2:B41")
Set test = Range(Rng.Offset(1, 0), Cells(lastrow, Rng.Column))

For a = LBound(LookArr, 1) To UBound(LookArr, 1)
test.Replace What:=LookArr(a, 1), Replacement:=LookArr(a, 2), LookAt:=xlPart, SearchOrder:=xlByRows
Next a

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox Timer - start

End Sub

As you can see, i've put the companynames and their replacementvalues in a worksheet. This was easier that putting it all in the code.

regards
Tommy Bak

 

[Legacy 21301]

So what about if you sort the data and then find/replace (or filter) ?

Or perhaps (either after sorting or not) use another column to enter a formula to identify a specific value and then (either after sorting or not, by the formula column) replace all the cells containing that value via one line of code?

The point is that there are many other ways to do it and the most efficient way may vary depending upon the make-up of the data.

It is also a question of the difference in the run times.
If one way is twice as fast as another, it doesn't mean much if the run time of the fastest way is 0.2 seconds

 

[Ekim]

Shades,

Time for one more?

In the following procedure, you have a worksheet named “Setup” that contains your current company names in column A and the replacement names in column B. The data in columns A and B is named “MyRng”, a dynamic rangethat increases/decreases as you add/delete data.

Replace - WS reference.xls
	A	B	C	D
1
2	CurrentNames	Replacewith
3	CompanyAAAAAcmeSuppliesLtd	InternationalAAAALtd
4	CompanyBBBB1234PtyLtd	InternBBBB
5	CompanyCCCCAltasInc	InternationalSuperAtlasCCCCInc
6	CompanyDDDDInc	InternationalDDDD
7
Setup

Macro in a standard module:

Sub replaceNames()
Dim myReplace As Variant
Dim i As Integer

Application.ScreenUpdating = False

 With ThisWorkbook.Worksheets("Setup")
    myReplace = .Range("MyRng").Value
 End With

 For i = 1 To UBound(myReplace)
    If Not IsEmpty(myReplace(i, 1)) Then
      ActiveSheet.Cells.Replace What:=myReplace(i, 1), _
             Replacement:=myReplace(i, 2)
    End If
 Next i

Application.ScreenUpdating = True

End Sub

From an administration point of view, maintaining company names in a worksheet may be easier than hard-coding the names in a macro, particularly as you state that company names may be “one word, two words, or in a few cases five words” (hard-coding your 40 company names, and 40 replacement names, would drive me nuts).


See this line starting with:

ActiveSheet.Cells

The macro looks at the entire worksheet.

You may wish to restrict the macro to a specific column:

ActiveSheet.Range("A:A")

I will be interested to know how the macro performs against your data. Perhaps there is a trade-off between macro speed and maintaining the data.

Regards,


Mike