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.)

 

[santeria]

The Archives here have some material on find and replace...

This link has some details on the limitations:

http://www.mrexcel.com/board2/viewtopic.php?t=71443&postdays=0&postorder=asc&start=20

However, if you do a search in this boards archive there is more, but exactly how pertinant to what you want to do, I am not sure.

 

[shades]

Thanks for the link. That wasn't the issue. The most in any cell was 40 characters.

HOWEVER, I discovered something. On an earlier project I had changed Options on Calculation to Manual - and then forgot about it. Well, I went back in and changed it to "Automatic". That did it. Now the entire Find/Replace macro did the entire project in less than a minute!!

Who would have thought? Not me. But I learned something.

 

[herilane]

You can also turn off screen updating for the duration of your macro (Application.ScreenUpdating = False). Don't forget to set it back to true when you're done.

 

[shades]

If you notice in my OP, screen updating was turned off.

 

[just_jon]

Can you replace "Company " with nothing, ie a single replace, or are there other instances of the target string? Or is the Company bit just a reprsentation...

 

[shades]

It is a representation. The string "Company AAAA" may be one word, two words, or in a few cases five words. And there are multiple instances of each. In at least seven cases the name will appear at least 1,000 times.

 

[just_jon]

What columns/rows can the targets be in? All? Any calculation going on?

 

[shades]

The column can vary from spreadsheet to spreadsheet; within one spreadsheet it will always be the same column. Usuallythe 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).

There were no calculations of any kind in this specific spreasheet. The only way I found out that it was Manual Calculation was when I tried to determine how many characters in each company name, I used =LEN(B1) and filled down the column. Nothing happened. When I turned on Automatic Calculation, the formulas instantly changed. I then clicked my macro button - and within a minute all company names were changed (i.e. worked liked it normally did).

 

[just_jon]

So, it's now running well? [ I wouldn't think ~15k rows should kill XL, but maybe you had a bunch of other apps in background? ]