SOLVED
Home

Speed of assigning values to cells?

%3CLINGO-SUB%20id%3D%22lingo-sub-673134%22%20slang%3D%22en-US%22%3ESpeed%20of%20assigning%20values%20to%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673134%22%20slang%3D%22en-US%22%3E%3CP%3ENewbie%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20macro%26nbsp%3Bwhich%20gathers%20data%20from%20a%20variety%20of%20places%20and%20then%20builds%20a%20summary%20table.%26nbsp%3B%20The%20calculations%20are%20extremely%20fast.%26nbsp%3B%20That%20is%2C%20if%20I%20don't%20DO%20anything%20with%20the%20counts%2C%20then%20the%20macros%20is%20over%20in%20the%20blink%20of%20an%20eye.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20if%20I%20write%20those%20calculations%20into%20the%20summary%20table%2C%26nbsp%3Bit%20goes%20very%2C%20very%20slowly.%26nbsp%3B%20The%20part%20of%20the%20code%20shown%20below%20takes%20about%2030%20seconds%20to%20complete.%26nbsp%3B%20I%20can%20count%20about%20half%20a%20second%20between%20each%20cell%20as%20I%20watch%20them%20being%20populated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BDim%20MyRow%2C%20cntVar1%2C%20cntVar2%2C%20cntVar3%2C%20cntVar4%2C%20cntVar5%20As%20Integer%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BFor%20MyRow%20%3D%206%20To%2020%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'%20Calculate%20cntVar1%20to%20cntVar5%26nbsp%3Bhere%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'Now%20display%20them%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Worksheets(%22Sheet1%22).Activate%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Cells(MyRow%2C%204).Value%20%3D%20cntVar1%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Cells(MyRow%2C%205).Value%20%3D%20cntVar2%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Cells(MyRow%2C%206).Value%20%3D%20cntVar3%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Cells(MyRow%2C%207).Value%20%3D%20cntVar4%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Cells(MyRow%2C%208).Value%20%3D%20cntVar5%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BNext%20MyRow%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20without%20those%20five%20lines%20of%20Cells().Value%20assignments%2C%20the%20code%20is%20very%20fast.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20cells%20in%20question%20are%20empty%20prior%20to%20the%20macro%20being%20run%20and%20there's%20no%20conditional%20formatting%20going%20on%20-%20or%20anything%20else%20that%20I%20can%20imagine%20that%20is%20causing%20a%20lot%20of%20processing%20between%20the%20population%20of%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20I%20doing%20something%20wrong%3F%26nbsp%3B%20Is%20there%20a%20faster%20way%20to%20assign%20values%20to%20a%20bunch%20of%20cells%3F%3C%2FP%3E%3CP%3EThanks%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-673134%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673168%22%20slang%3D%22en-US%22%3ERe%3A%20Speed%20of%20assigning%20values%20to%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673168%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347015%22%20target%3D%22_blank%22%3E%40bvelke%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Eturn%20screenupdating%20to%20false%20at%20the%20beginning.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20Macro()%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3BApplication.screenupdating%20%3D%20false%3CBR%20%2F%3EApplication.Calculation%20%3D%20xlCalculationManual%3CBR%20%2F%3E%3CBR%20%2F%3E'Your%20code%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.Calculation%20%3D%20xlCalculationAutomatic%3CBR%20%2F%3EApplication.Screenupdating%3Dtrue%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20title%3D%22VBA-Tanker%22%20href%3D%22http%3A%2F%2Fwww.vba-tanker.com%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-tanker.com%20-%20a%20database%20full%20of%20usefull%20excel-macros%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673321%22%20slang%3D%22en-US%22%3ERe%3A%20Speed%20of%20assigning%20values%20to%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673321%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3BThat%20makes%20a%20huge%20difference%20%3A-)%26nbsp%3B%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
bvelke
Occasional Contributor

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.

2 Replies
Solution

@bvelke 

Hi,

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

@Berndvbatanker That makes a huge difference :-)  Thanks!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies