Speeding up macros (find & replace)

  • Hi. I have a macro which does the job I ask of it but I'd be grateful if anyone here could advise on how to speed it up. It's basically a find and replace on around 370,000 cells (1600 rows by 230 columns). I ran it on a subset and it took a bit under half an hour to do 46 columns. For all I know, this is as fast as it will go and it's not really a major problem, I can just go and make a cup of tea, or two, but I'd be interested to know if anything I've done is particularly cumbersome or contributing to its efficiency. (I picked up a useful tip on this site on turning on manual calculation.)

    To sum up what I'm doing, each of the 1600 cells in each column is linked to the value of a cell in another file. Each column is linked to a different file but the cell references are the same so the macro just copies the 'link' formulae across all 230 columns and then replaces the file name in each. It may not be an ideal starting point but that's what I've inherited.

  • Re: Speeding up macros (find & replace)

    it appears that you are changeing each and every cell by itself. I suggest you change the first row only and then fill down. That is much faster when done manually so i suppose it will be the same through code.

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

  • Re: Speeding up macros (find & replace)

    Thanks h1h. I'll bear that in mind thought it's complicated by the fact that the link references are not all consecutive. Btw these forums do fill up quickly - seven pages already!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!