Jun 05 2019 09:41 PM
Newbie here.
I have a macro which gathers data from a variety of places and then builds a summary table. The calculations are extremely fast. That is, if I don't DO anything with the counts, then the macros is over in the blink of an eye.
But if I write those calculations into the summary table, it goes very, very slowly. The part of the code shown below takes about 30 seconds to complete. I can count about half a second between each cell as I watch them being populated.
Dim MyRow, cntVar1, cntVar2, cntVar3, cntVar4, cntVar5 As Integer
For MyRow = 6 To 20
' Calculate cntVar1 to cntVar5 here
'Now display them
Worksheets("Sheet1").Activate
Cells(MyRow, 4).Value = cntVar1
Cells(MyRow, 5).Value = cntVar2
Cells(MyRow, 6).Value = cntVar3
Cells(MyRow, 7).Value = cntVar4
Cells(MyRow, 8).Value = cntVar5
Next MyRow
Again, without those five lines of Cells().Value assignments, the code is very fast.
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.
Am I doing something wrong? Is there a faster way to assign values to a bunch of cells?
Thanks for any help.
Jun 05 2019 10:44 PM
SolutionHi,
turn screenupdating to false at the beginning.
Sub Macro()
Application.screenupdating = false
Application.Calculation = xlCalculationManual
'Your code
Application.Calculation = xlCalculationAutomatic
Application.Screenupdating=true
End Sub
Best regards
Bernd
www.vba-tanker.com - a database full of usefull excel-macros
Jun 06 2019 12:41 AM
@Berndvbatanker That makes a huge difference :) Thanks!
Jun 05 2019 10:44 PM
SolutionHi,
turn screenupdating to false at the beginning.
Sub Macro()
Application.screenupdating = false
Application.Calculation = xlCalculationManual
'Your code
Application.Calculation = xlCalculationAutomatic
Application.Screenupdating=true
End Sub
Best regards
Bernd
www.vba-tanker.com - a database full of usefull excel-macros