[excel] - autofill speed?

I have a macro contains the following lines for autofilling:


    Range("D2:I2").AutoFill Destination:=Range("D2:I9"), Type:=xlFillDefault
    Range("D22:I22").AutoFill Destination:=Range("D22:I29"), Type:=xlFillDefault
    Range("D42:I42").AutoFill Destination:=Range("D42:I49"), Type:=xlFillDefault
    Range("D62:I62").AutoFill Destination:=Range("D62:I69"), Type:=xlFillDefault
    Range("D82:I82").AutoFill Destination:=Range("D82:I89"), Type:=xlFillDefault


The macro itself is in a loop of running around 1000 times.
The macro is basically like this, everytime, the D2:I2, D22:I22..etc data are changed, and then autofilled... and then the data changed again, and then autofilled again...

My question is, why does my macro run @ a very fast speed @ the beginning, however, after around 200 times, it slows down dramatically. Why is that??
my cpu is duron 1ghz.
excel 2000

any ideas why?? can i make it run consistently fast??
because the speeds that it slows down to is very pathetic.. it takes like several seconds to autofill few lines..



   
c_kent301Asked:
Who is Participating?
 
ehoutConnect With a Mentor Commented:
Hi,

Sorry it took a while.
I'm still looking at your sheets at the moment, but it's not an easy fix. To make things faster, you'll have to rebuild the code. It seems like your pretty experienced in using Excel, but a bit less in programming VBA?

Tip: keep the number of commands to be processed at a minimum. For eaxample, you have a loop iterating hundreds of time. Instead of setting the pplication.screenupdating.false inside this loop. place it outside.
Also byundt has a point with the references to the workbook (sheet and range objects). Worksheet functions often have nice functionality, but each reference to the worksheet, workbook, or any range takes time to process. And since you are iterating hundreds of times...

But all these optimizations will not shorten the running time as much as you want, since you are doing a lot of stuff. So best is to skip running on the worksheet at all and use the method byundt mentioned, namely by use of arrays.

0
 
byundtCommented:
From past experience, when VBA programs run too slowly, it's usually time to change the algorithm to something more efficient. Why do you need to loop through the AutoFill 1000 times?
0
 
c_kent301Author Commented:
but why does it start out so fast? and then gets slower & slower??
I thought if the algorithm isn't efficient, it should be slow all the way...
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ehoutCommented:
Well,
It may have to do with your screenupdating? or that excel holds the undo information and thus is using more and more resources?
Try putting this:
application.screenupdating=false
before the loop and
application.screenupdating=true
after it and in your errorhandling.

Also you might consider
the following before your code

dim lngCalc&
dim blnautoComp as boolean
with application
  lngcalc& = .Calculation
  blnautoComp = .AutoComplete
  .Calculation = xlManual
  .AutoComplete = False
end with

again, after your code (and in your errorhandling) you should reset this, so put there:

with application
  .Calculation = lngcalc&
  .AutoComplete = blnautoComp
end with

Kind regards
0
 
c_kent301Author Commented:
ehout,
thanx for your reply..
I have already disabled the screenupdat...

however, i didn't have this
==============
dim lngCalc&
dim blnautoComp as boolean
with application
 lngcalc& = .Calculation
 blnautoComp = .AutoComplete
 .Calculation = xlManual
 .AutoComplete = False
end with
===============
can you tell me what this is for??
and..

I copied/pasted it in my macro,
but there's something wrong with:

 blnautoComp = .AutoComplete



 .AutoComplete = False


Something wrong also happens for these lines:
 
 .Calculation = lngCalc&
 .AutoComplete = blnautoComp


0
 
c_kent301Author Commented:
"that excel holds the undo information and thus is using more and more resources?"

I think this could be it..
but don't know how to clear it..
can you help me w/ it?
thanx a lot
0
 
byundtCommented:
The extra code turned your calculation from automatic to manual. When you change a cell with manual calculation, it will resolve its own formula, but not those of any cells that refer to it. With automatic (or semi-automatic) calculation, excel will resolve the entire spreadsheet after each change.

If all you are doing is pasting data, then it makes no difference. On the other hand, if your spreadsheet formulas refer to the range being filled--then it could slow things down considerably.

The .Calculation=IngCalc& trys to restore whichever calculation mode you had before changing it to manual. Unless you are using the Table feature (one input or two input), then there is no difference between automatic & semi-automatic. Rather than figure out the error, you might change the line to:

application.calculation=xlAutomatic

This latter line is more reliable if your code blows up (thus trapping you in manual calculation mode).
0
 
ehoutCommented:
byundt is right about the code.
However, I'm a bit surprised you got errors. What errors did you get?

If often use the way above to change any setting in an Office program and later on restore it to the previous one. (create VBA for more people than just me, and cannot rely on people not having changed some settings).

If the code keeps erroneous, you can achieve the same thing by switching autocalculation and autocomplete off in the options dialog.

I'm not sure if the undo stuff can be switched off in VBA.
But I think the easiest way is just not to use the workbook functions, but create a loop yourself to achieve what you want.

Cry out loud if you need an example for this.

Kind regards.
0
 
c_kent301Author Commented:
Hey guys,

thanx for both of your repllies...

however, it still slows down after around 200 cycles...


I've uploaded an example of my file to

http://www.geocities.com/c_kent301/data.xls


can you guys check it out and see if the same thing happens to you guys? like the whole thing begins to slow down after few hundreds of cycles?? and if there's anything that i did wrong or can be improved, please help me to correct it.. thanx.


the macro I run is

replace_autofill

(the code is following)


======================
Sub replace_autofill()
   Dim wshSheet2           As Worksheet
   Dim intInitialValue     As Integer
   Dim intFinalValue       As Integer
   Dim i                   As Integer 'counter
   Dim int6P1Row        As Integer
   Dim btest   As Boolean
   Set wshSheet2 = ThisWorkbook.Sheets("Sheet2")
   
   intInitialValue = Sheet2.Range("K10")
   intFinalValue = Sheet2.Range("L10")
   int6P1Row = 2
   
   For i = intInitialValue To intFinalValue
       
    '   Application.Run "AutoClear"
       
    j = i + 1
   
    Range( _
        "D2:I2,D22:I22,D42:I42,D62:I62,D82:I82,D102:I102,D122:I122,D142:I142,D162:I162,D182:I182,D202:I202,D222:I222" _
        ).Select

    Selection.Replace i, _
        Replacement:=j, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        MatchCase:=False
       
       
       
       'autofill
        Application.Run "AutoFill"

 
   









Application.ScreenUpdating = False










   
   
   
   
   
   
   
   
   
   
' 6P1 CHECKING START..

       If Sheet2.Range("U3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("D2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If
   
   
        If Sheet2.Range("V3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("E2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If
   
   
        If Sheet2.Range("W3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("F2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If

        If Sheet2.Range("X3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("G2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If
   
        If Sheet2.Range("Y3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("H2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If

        If Sheet2.Range("Z3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("I2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If
   
' 6P1 CHECKING ENDED..
   
   





Application.ScreenUpdating = True














   Next i
   

Application.Run "AutoClear"

   
    Range( _
        "D2:I2,D22:I22,D42:I42,D62:I62,D82:I82,D102:I102" _
        ).Select

        Selection.Replace j, _
        Replacement:=3, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        MatchCase:=False






   
End Sub






0
 
c_kent301Author Commented:
ehout,

what do you mean by

"the easiest way is just not to use the workbook functions, but create a loop yourself to achieve what you want." ??

like, what do you mean not to use the workbook functions??



0
 
byundtConnect With a Mentor Commented:
First, I notice from your sample code that you are using quite a few dot commands (e.g.  .Select). Microsoft suggests minimizing the use of dot commands to improve execution speed. For example, you frequently use:

           Sheets("Sheet2").Select

Each of these references could be eliminated by using

With Sheets("Sheet2")
...
End With

Second, you may want to change your algorithm to minimize the interaction between VBA and the spreadsheet. In one program I wrote, this change resulted in almost ten-fold improvement in execution speed.

There's a fair bit of overhead each time you switch between the Excel spreadsheet and VBA. If you can do all of your manipulation in VBA (without using the spreadsheet to store the intermediate values) then execution speed will increase substantially. The basic approach is to read the data from the spreadsheet into VBA, do the calcs in VBA, then return a single big block of results to the spreadsheet.

To do this, Dim an array Results(222,26)   (I picked those subscripts by looking for the highest row & column references in your sample code). Read as much starting data into VBA as you will need with statements like: Results(i,j)=range("A1").

Once all the data has been entered into VBA, you can then do the manipulations shown in your code above (such as looking for the text string "6X" and then assigning values to the appropriate elements).

When all calcs are complete, paste the results back onto the spreadsheet with a statement like: Range("A1","Z222").value=Results

The above approach assumes that the receiving spreadsheet has been pre-formatted correctly. If need be, however, you can format programmatically--though that will slow things down.
0
 
ehoutCommented:
Hi,

Having seen what your excel sheet looks like, I'd say: optimize it as much as you can, using the remarks given in the comments above. You will gain a little performance, but don't expect miracles.

The stuff is pretty complex, even with help of arrays. If you're not an experienced programmer, just optimize a little be glad with it, and just be patient while it runs.

Kind regards.
0
 
byundtCommented:
I tried looking for your workbook using the posted link, but got a "This page is not available" message. Could you repost it please?
0
All Courses

From novice to tech pro — start learning today.