Excel 2007 : Numeric cell entry does not respond for 10 seconds

[Dunkirk]

I have a worksheet in a workbook containing 15 worksheets. 14 of the 15 are no problem, however one worksheet containing about 350 entries starting giving me a problem last week. When I enter a numeric value in a cell it does not respond for about 10 seconds. Any idea what the problem is?

[tigeravatar]

Dunkirk,

Welcome to the forum!
The most common items that can slow a sheet down are:

formulas that reference whole columns
array formulas
worksheet event code

If you don't have any of that, then I'd have to see an example workbook that is experiencing the problem.

[Dunkirk]

Thank you tigervatar:

I think that need to send you the workbook. How do I do that?

Dunkirk

[tigeravatar]

To attach a file, click Go Advanced and then click the paper clip icon to open the Manage Attachments dialog

[Dunkirk]

tigerwatar

I think I have attached the file for you, I hope

Dunkirk

[tigeravatar]

Hmm.. I'm not seeing anything obvious, other than you have every single cell formatted to be Bold, Arial, 12 pt. But just copy/pasting the populated range of column A to a new sheet seems to take the slow down with it. I was only able to get rid of the slowdong using a macro that got just the populated data in column A formulas (which includes the raw values) and cell formats and pasted special it all into a brand new worksheet.

So, in a brand new Excel file, use the following macro. It will prompt you to select an Excel file. Select the one that contains the problem worksheet. Before you run the macro, make sure to update the strName from "AAA" to be the name of the actual worksheet.

Please Login or Register  to view this content.

How to use a macro:

1. Make a copy of the workbook the macro will be run on
   • Always run new code on a workbook copy, just in case the code doesn't run smoothly
   • This is especially true of any code that deletes anything
2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
3. Insert | Module
4. Copy the provided code and paste into the module
5. Close the Visual Basic Editor
6. In Excel, press ALT+F8 to bring up the list of available macros to run
7. Double-click the desired macro (I named this one OnlyKeepEveryThirdRow)

There, now you've run the macro, the file is open, and you have the sheet (OLD) and the sheet (NEW). the sheet (NEW) should be much faster. If that works, you can delete the sheet (OLD).

[Dunkirk]

Thank You tigervater:

I will try to do as you suggested. In the meantime I am copying month by month into a new worksheet. I'll do a month a day while I am on vacation.

Thank you so much for your help and time

Dunkirk