 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2007problems with ClearContents in Excel 2003
Help! When using Excel 2003, it takes a very long time for me to clear the contents of a large number of cells. The delay occurs whether I clear the contents manually in Excel, or within a VBA macro. This problem began several weeks ago and has persisted. For example, when I clear 20,000 rows in a moderately large workbook (12Meg) it takes 10 minutes. I ran the same macro on a co-workers computer (exact same workbook) and it took less than 0.2 seconds. I reinstalled Office 2003 and this did not help (no impact) This is driving me crazy.... I have an up to date computer (2 meg memory) and I am using XP pro... have disabled Google desktop (no impact), and have very little unusual on my machine. one possible clue... when I use a macro that clears, say, 100 rows at a time, and set a timer, the length of time it takes to perform the clearcontents function increases with each subsequent call. For example the first 100 rows takes 0.3 seconds.. the next 100 rows takes 0.6 seconds, and so on. Any suggestions will be greatly appreciated. ...Rick Hi You could switch Calculation to Manual before running the macro, Tools>Options>Calculation>Manual then switch back to automatic afterwards.  SignatureRegards Roger Govier > Help! When using Excel 2003, it takes a very long time for me to > clear the contents of a large number of cells. The delay occurs [quoted text clipped - 21 lines] > Any suggestions will be greatly appreciated. > ...Rick Thanks for the reply. The calculation is set to Manual. It is not recalculating... I have been monitoring and timing that separately. It is doing something else. I just don't know what it is doing. I thought about turning off the Undo function, but I don't know how to do that... but note that there is no long time lag when I run this on someone else's computer, so I don't think it is just the Undo function. On Sep 5, 5:47 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk> wrote: > Hi > [quoted text clipped - 37 lines] > > - Show quoted text - Rick, If there's a lot of calculation going on as a result of your clearing the cells, try this: Application.Calculation = xlCalculationManual ' your cell-clearing code here Application.Calculation = xlCalculationAutomatic There's a danger here. If an error stops your code, calculation will remain set to manual. Not good. If there's any chance of that, then error processing code should reset calculation to automatic. Something like: On Error GoTo errr Application.Calculation = xlCalculationManual ' your cell-clearing code here Application.Calculation = xlCalculationAutomatic End errr: Application.Calculation = xlCalculationAutomatic MsgBox "Error " & Err.Number & " has occurred. " & Err.Description End SignatureRegards from Virginia Beach, Earl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- > Help! When using Excel 2003, it takes a very long time for me to > clear the contents of a large number of cells. The delay occurs [quoted text clipped - 21 lines] > Any suggestions will be greatly appreciated. > ...Rick Thanks for the reply. The slowdown occurs with calculation already set to manual. Just to make sure I was not missing something I did try adding the suggested code to no effect. This is not a calculation issue, but something else. generally since there are a lot of calculations within the workbook, I leave the calculation setting to manual as the default so that I can control when the calculation occurs. > Rick, > [quoted text clipped - 57 lines] > > Any suggestions will be greatly appreciated. > > ...Rick I'm not sure if this will help, but it shouldn't take long to test. Saved from a previous post. If you can see the pagebreak dotted lines, then excel will slow down. If you're in View|Page break preview mode, then excel will slow down. Turning off .screenupdating and changing the .calculationmode to manual may help speed things up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'your code here 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode Application.ScreenUpdating = True End Sub > Help! When using Excel 2003, it takes a very long time for me to > clear the contents of a large number of cells. The delay occurs [quoted text clipped - 21 lines] > Any suggestions will be greatly appreciated. > ...Rick SignatureDave Peterson Thanks! regretably, turning off the updating or calculation had no impact. If I clear 5000 rows, 500 at a time, it takes 0.4 seconds for the first 500 rows and 6.4 seconds for the last 500, and more than 34 seconds for all 5000. Obviously something is wrong. > I'm not sure if this will help, but it shouldn't take long to test. > [quoted text clipped - 62 lines] > > - Show quoted text - This did more than turn off calculation. Did you try it? > Thanks! regretably, turning off the updating or calculation had no > impact. If I clear 5000 rows, 500 at a time, it takes 0.4 seconds for [quoted text clipped - 67 lines] > > > > - Show quoted text - SignatureDave Peterson I used exactly the code you described, and there was no impact. 35 seconds to clear 5000 rows seems much too long. Note the pattern of more time elapsed the farther down in the sheet. My Code: Sub MyClear() sName = "Resrv95" Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ClearRow = 32 nclear = 500 t0 = Timer() t1 = t0 OutRow = 3 While ClearRow <= 5000 clearRow2 = ClearRow + nclear TheRange = "A" & Format(ClearRow, "#") & ":CZ" & Format(clearRow2, "#") Worksheets(sName).Range(TheRange).ClearContents ClearRow = clearRow2 t2 = Timer() aud.Cells(OutRow, 20) = t2 - t1 aud.Cells(OutRow, 21) = t2 - t0 aud.Cells(OutRow, 19) = ClearRow OutRow = OutRow + 1 t1 = t2 Wend Application.Calculation = CalcMode ActiveWindow.View = ViewMode Application.ScreenUpdating = True End Sub The output: 532 0.44 0.44 1032 1.14 1.58 1532 1.81 3.39 2032 2.47 5.86 2532 3.13 8.98 3032 3.80 12.78 3532 4.48 17.27 4032 5.14 22.41 4532 5.80 28.20 5032 6.44 34.64 > This did more than turn off calculation. > [quoted text clipped - 79 lines] > > - Show quoted text - Try adding one more line to see if that helps. Right after the xlNormalview line, but before your real code: ActiveSheet.DisplayPageBreaks = False I meant to include that in the original suggestion, but didn't. > I used exactly the code you described, and there was no impact. 35 > seconds to clear 5000 rows seems much too long. Note the pattern of [quoted text clipped - 132 lines] > > > > - Show quoted text - SignatureDave Peterson Thanks for your interest. There was no impact from that suggestion. still 35 seconds to clear 5000 rows. > Try adding one more line to see if that helps. > [quoted text clipped - 148 lines] > > - Show quoted text - Sorry it didn't work. > Thanks for your interest. There was no impact from that suggestion. > still 35 seconds to clear 5000 rows. [quoted text clipped - 151 lines] > > > > - Show quoted text - SignatureDave Peterson This isn't going to help you much, but you are not alone. A co-worker and myself have the exact same problem. We've tried everything you have. We consider ourselves more than proficient Excel and VB folks. And we are completely stumped. Here's an oddity: I have searched the net far and wide and the ONLY references I can find to similar problems (and there are only a few) are very recent - the last couple of weeks....don't know what that means, but it's odd. EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.