Speed of assigning values to cells?

Question

Newbie here.&nbsp;I have a macro&nbsp;which gathers data from a variety of places and then builds a summary table.&nbsp; The calculations are extremely fast.&nbsp; That is, if I don't DO anything with the counts, then the macros is over in the blink of an eye.&nbsp;But if I write those calculations into the summary table,&nbsp;it goes very, very slowly.&nbsp; The part of the code shown below takes about 30 seconds to complete.&nbsp; I can count about half a second between each cell as I watch them being populated.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dim MyRow, cntVar1, cntVar2, cntVar3, cntVar4, cntVar5 As Integer&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;For MyRow = 6 To 20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' Calculate cntVar1 to cntVar5&nbsp;here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Now display them&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Worksheets("Sheet1").Activate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MyRow, 4).Value = cntVar1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MyRow, 5).Value = cntVar2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MyRow, 6).Value = cntVar3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MyRow, 7).Value = cntVar4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MyRow, 8).Value = cntVar5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Next MyRow&nbsp;Again, without those five lines of Cells().Value assignments, the code is very fast.&nbsp;The cells in question are empty prior to the macro being run and there's no conditional formatting going on - or anything else that I can imagine that is causing a lot of processing between the population of cells.&nbsp;Am I doing something wrong?&nbsp; Is there a faster way to assign values to a bunch of cells?Thanks for any help.

berndvbatanker · Accepted Answer

bvelke&nbsp;Hi,turn screenupdating to false at the beginning.&nbsp;Sub Macro()&nbsp;Application.screenupdating = falseApplication.Calculation = xlCalculationManual'Your codeApplication.Calculation = xlCalculationAutomaticApplication.Screenupdating=trueEnd Sub&nbsp;Best regardsBerndwww.vba-tanker.com - a database full of usefull excel-macros

Forum Discussion

Speed of assigning values to cells?

2 Replies

Resources