Excel - sheet gets slow after a while

[dmkAlex]

I have this sheet that I download a large number of rows of records from another program everyday. I programmed in the delete rows to clean up old record before copy/paste the new record into the sheet. The sheet has 20 columns of data.

This will work for a while, but after a month or two, the sheet gets very slow. Particularly when the cursor is in the far right and far down position. The cursor seems to get stuck in the cell. When I use the arrow key to move the cursor, there is a prolong delay. When my vba macro runs, sometimes, a normal operation which flow thru like a breeze would just get stuck. In worst case scenerio, it would even time out and gives me "sheet not responding" error.

I fix it by recreating a new sheet and deleting the old sheet. But I am curious why it's doing that and how can I avoid this from happening. Is this an Excel bug?

By the way, it is Excel 2003, Window XP Home, with 2 gb of ram.

Alex

 

[carrr]

What else is running on the machine when this happens? With the ram you specify, Excel shouldn't be hanging. Try sleuthing out other apps that are running and using resources. Shut them down or disable them. Are you free of spyware?

 

[xlhelp]

Not sure, but deleting the xlb file may eleviate this problem

Member- AAAA Association Against Acronym Abusers

 

[dmkAlex]

I don't think it is a ram or resource problem because when we did the fix by deleting the problem sheet and recreate a new sheet, the problem went away right the way without having had to close any other applications.

Of course we also had tried shut down, reboot routine and non of them solved the problem. Took us a while to find a fix.

Just curious how to avoid it at all.

Alex

 

[GlennUK]

You haven't got Track Changes switched on have you?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[dmkAlex]

No. This workbook was designed for single user in a single stand along machine.

Alex

 

[pbrodsky]

Just a random thought - have you tried doing a SaveAs on the file - in many programs that cleans out a lot of stuff like saved-up undos etc.

 

[dmkAlex]

Do you mean saveAs to the same file name, or a different name?

Alex

 

[pbrodsky]

I've heard that either works the same way - don't have any real personal experience.

 

[xlhelp]

Do you have

Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False

at the beginning of your macro and

Application.ScreenUpdating = True
ActiveSheet.DisplayPageBreaks = True


before the end of your macro?

If not, give that try.



Member- AAAA Association Against Acronym Abusers

 

[dmkAlex]

I do use the screenupdating function a lot in my macro. And I am pretty sure I turn it to Ture at the end of the macro. I may have missed some.

Would that matter that much?

Alex

 

[GlennUK]

When it's getting slow, do Ctrl-End in the sheet and see how far down your active cell goes. Does it go further and further down the sheet as the processing gets slower?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[dmkAlex]

When we tried editing some cells, it got slowest in the far right and far down cells.

Alex

 

[xlhelp]

Each time you insert/delete/hide/unhide a row or
column Excel has to recalculate where the page breaks are.

Excel becomes excruciatingly slow if you Print preview on top of everything

I would suggest that as I have indicated above, you turn off screen display and PrintPreview. Seeting the caluclation to Manual would also be good idea and setting it back to automatic at the end of macro.

And, you didn't quite answer what Glenn asked. He asked if the lastcell (adderess) changes every time you run the macro. Does it get further and further down? If what Glenn asked is true then you also need to reset lastcell every time you run your macro. Look at David McRitchie's write up on that:

http://www.mvps.org/dmcritchie/excel/lastcell.htm#makelastcell

Member- AAAA Association Against Acronym Abusers