Macro to Find and Replace - Speed

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
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.

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

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
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

New Member
Joined
Dec 30, 2003
Messages
21
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

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
If you notice in my OP, screen updating was turned off.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
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

Legend
Joined
Sep 3, 2002
Messages
10,473
What columns/rows can the targets be in? All? Any calculation going on?
 

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
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

Legend
Joined
Sep 3, 2002
Messages
10,473
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? ]
 

Watch MrExcel Video

Forum statistics

Threads
1,095,960
Messages
5,447,568
Members
405,456
Latest member
melmelseh

This Week's Hot Topics

Top