Poor Macro Performance

I have an excel sheet containing macros. I have used it several times. But presently when i am trying to run macros, gradually running speed decreasing and finally getting hanged. I am attaching this file. Please go through it and give some solution. Presently I am using Excel-2010. Macros are in columns U, W, Y in the sheet "INPUT_SUPER". To run Macros we have 2 press and hold Ctrl+shift+Q.

There's no code in that sheet

Your code is confusing to me. Where is the active cell supposed to be when you run this?

And, what is it supposed to do?

Hi,
You have 9884 formulas on the Input_Super sheet alone. That is a lot. You also have bad Range Names. It looks like your work needs cleaning up. I was able to Open Excel and then Open AND Repair which seemed to help some.

The code is in Module 2 not behind the sheet.

I think you just have so many formulas on the Input_Super sheet that it slows down when trying to recalculate and takes a long time and then dies.

Try turning Calculation to Manual and do things to see if it helps the speed.

I think you have more problems with the number of formulas than with your macro.

Try to save the workbook in the 2010 format of .xlsx and see if that helps. Find the attached.

Hello friend, the sheet u had sent in excel-2010 version is giving the same problem. But yesterday I found that it is running well in excel-2003 and excel-2004. But it is getting hanged when I m running it in Excel-2007 & 2010. Could u pls give me a solution.

Hi,

I have a utility called PUP from John Walkenbach that I ran on your workbook. I'm attaching this report.

It seems you have hidden named ranges (see Name Report tab) and even ranges that have bad Refs. I'd clean those up first.

Then on the Worksheet Report tab it shows you have 30192 formulas across your 12 sheets. You have hidden columns and hidden rows on some sheets, I'd unhide all those also to do a cleanup.

With the number of sheets (greater than 2) and number of formulas (greater than 10) - you are on your own. I can't imagine anyone wanting to tackle this big of a problem (for free).

You should try to understand the big picture of what is needed and simplify, simplify, simplify. The workbook was so big and slow, I never got a chance to look at the macros.

good luck.

ckm08 - welcome to the board - going forward please try to keep thread titles relevant to the underlying problem (here that would seemingly be performance)

on this occasion - given first thread - I've modified for you but please bear above rule in mind (and all other Forum Rules)

Originally Posted by ckm08

I found that it is running well in excel-2003 and excel-2004. But it is getting hanged when I m running it in Excel-2007 & 2010

I've not looked at your file but note that sheet size jumps dramatically from XL2007 onwards... perhaps that's a good place to start ?
(ie see if you're looping / processing large ranges [unnecessarily])