Excel VBA Code performance is Extremely Slow

excel - VBA - Code Execution is Extremely Slow - Stack Overflow Resultsfromthe2022DeveloperSurveyarenowavailable Home Public Questions Tags Users Companies Collectives ExploreCollectives Teams StackOverflowforTeams –Startcollaboratingandsharingorganizationalknowledge. CreateafreeTeam WhyTeams? Teams CreatefreeTeam Collectives™onStackOverflow Findcentralized,trustedcontentandcollaboratearoundthetechnologiesyouusemost. Learnmore Teams Q&Aforwork Connectandshareknowledgewithinasinglelocationthatisstructuredandeasytosearch. Learnmore ExcelVBACodeperformanceisExtremelySlow AskQuestion Asked 1year,4monthsago Modified 1year,4monthsago Viewed 321times 2 IhavewroteacodewhichisworkinglikeTurtlewalks.IhaveaddedApplicationFunctionstomakeitfasterbutcodehasdecidedthathehastoworkslowly. Anyexperthelpwillbeappreciated. DimLastRowColumnAAsLong DimiAsLong Application.ScreenUpdating=False Application.Calculation=xlCalculationManual LastRowColumnA=Sheet1.Cells(Rows.Count,1).End(xlUp).Row Fori=11ToLastRowColumnA IfSheet1.Cells(i,1).Value<>""Then Cells(i,7)=Evaluate("=INDEX(Table1!$A$1:$DP$27,MATCH($G$9&$E$4,Table1!$E:$E&Table1!$F:$F,0),MATCH(A"&i&",Table1!$6:$6,0))") Cells(i,8)=Evaluate("=INDEX(Table1!$A$1:$DP$27,MATCH($H$9&$E$4,Table1!$E:$E&Table1!$F:$F,0),MATCH(A"&i&",Table1!$6:$6,0))") Cells(i,9)=Evaluate("=INDEX(Table1!$A$1:$DP$27,MATCH($I$9&$E$4,Table1!$E:$E&Table1!$F:$F,0),MATCH(A"&i&",Table1!$6:$6,0))") Cells(i,10)=Evaluate("=INDEX(Table1!$A$1:$DP$27,MATCH($J$9&$E$4,Table1!$E:$E&Table1!$F:$F,0),MATCH(A"&i&",Table1!$6:$6,0))") Application.Calculation=xlCalculationAutomatic Application.ScreenUpdating=True EndIf Next secondapproach. DimLastRowColumnAAsLong LastRowColumnA=Sheet1.Cells(Rows.Count,4).End(xlUp).Row Sheet1.Range("G10").FormulaArray=_ "=IFERROR(INDEX(Table1!R1C1:R27C120,MATCH(R9C7&R4C5,Table1!C5&Table1!C6,0),MATCH(RC[-6],Table1!R6,0)),"""")" Sheet1.Range("G10").AutoFillDestination:=Sheet1.Range("G10:G"&LastRowColumnA),Type:=xlFillDefault Sheet1.Range("H10").FormulaArray=_ "=IFERROR(INDEX(Table1!R1C1:R27C120,MATCH(R9C8&R4C5,Table1!C5&Table1!C6,0),MATCH(RC[-7],Table1!R6,0)),"""")" Sheet1.Range("H10").AutoFillDestination:=Sheet1.Range("H10:H"&LastRowColumnA),Type:=xlFillDefault Sheet1.Range("I10").FormulaArray=_ "=IFERROR(INDEX(Table1!R1C1:R27C120,MATCH(R9C9&R4C5,Table1!C5&Table1!C6,0),MATCH(RC[-8],Table1!R6,0)),"""")" Sheet1.Range("I10").AutoFillDestination:=Sheet1.Range("I10:I"&LastRowColumnA),Type:=xlFillDefault Sheet1.Range("J10").FormulaArray=_ "=IFERROR(INDEX(Table1!R1C1:R27C120,MATCH(R9C9&R4C5,Table1!C5&Table1!C6,0),MATCH(RC[-9],Table1!R6,0)),"""")" Sheet1.Range("J10").AutoFillDestination:=Sheet1.Range("J10:J"&LastRowColumnA),Type:=xlFillDefault FormulasofFirstCellswhichhasbeenconvertedtocode. =IFERROR(INDEX(Table1!$A$1:$DP$27,MATCH($G$9&$E$4,Table1!$E:$E&Table1!$F:$F,0),MATCH(A10,Table1!$6:$6,0)),"") =IFERROR(INDEX(Table1!$A$1:$DP$27,MATCH($H$9&$E$4,Table1!$E:$E&Table1!$F:$F,0),MATCH(A10,Table1!$6:$6,0)),"") =IFERROR(INDEX(Table1!$A$1:$DP$27,MATCH($I$9&$E$4,Table1!$E:$E&Table1!$F:$F,0),MATCH(A10,Table1!$6:$6,0)),"") =IFERROR(INDEX(Table1!$A$1:$DP$27,MATCH($J$9&$E$4,Table1!$E:$E&Table1!$F:$F,0),MATCH(A10,Table1!$6:$6,0)),"") excelvba Share Follow editedFeb27,2021at19:31 askedFeb27,2021at19:01 user14807564user14807564 9 1 Application.Calculationaffectstheformulasonthesheet.Youarenottriggeringformulasonasheet,youarecalculatingthemyourselfwithEvaluate,soApplication.Calculationhasnoeffect.Ifyouwantedtoputtheformulainthecellsinsteadoftheresult,thenuse.FormulaandremoveEvaluate.Theformulaisalsothesameforallcells,soyoucanremovetheloop. – GSerg Feb27,2021at19:12 1 Theanswertothesequestionarealmostalwaystousevariantarraystocapturetheusedrangesanditeratethose,thisquestionisnodifferent.Atleastyoushouldlimitthefullcolumnreferencestojustthedataset.Youaredoingover8millioncalculationseveryloop.Thatisgoingtobeslow. – ScottCraner Feb27,2021at19:12 1 @GSergtheformulaisnotthesame,seethelastMATCH,itreferstoadifferentcell. – ScottCraner Feb27,2021at19:13 @ScottCranerThelastMATCHreferstothecellfromthesamerowinwhichtheformulaislocated.Thatisthesameformula,eventhoughitdoesnotlookthatwayintheA1notation(butitdoesintheR1C1notation). – GSerg Feb27,2021at19:15 1 Table1!$E:$E&Table1!$F:$FinyourformulashouldbeTable1!$E$1:$E$27&Table1!$F$1:$F$27Justdoingthatwillmakeitmuchquicker,butas@auxarystated,ifyoufindtherowusing4evaluatesbeforetheloop,like:ActiveSheet.Evaluate("MATCH($G$9&$E$4,Table1!$E$1:$E$27&Table1!$F$1:$F$27,0)")theninsidetheloopyouonlyneedtofindthecorrectcolumn:Application.Match(ActiveSheet.Range("A"&i),Worksheets("Table1").Range("6:6"),0)intoavariablethenitisasimpleActiveSheet.Cells(i,7)=Worksheets("Table1").Cells(rowFromEvaluate,ColumnFromApplicationMatch) – ScottCraner Feb27,2021at19:36  |  Show4morecomments 1Answer 1 Sortedby: Resettodefault Highestscore(default) Trending(recentvotescountmore) Datemodified(newestfirst) Datecreated(oldestfirst) 3 aspermycomment: Findtherowsoutsidetheloopastheywillallbethesame,thenjustfindthecolumnintheloop.Itwillcutdownonthenumberofcalc. DimLastRowColumnAAsLong DimiAsLong Application.ScreenUpdating=False Application.Calculation=xlCalculationManual WithSheet1 LastRowColumnA=Sheet1.Cells(Rows.Count,1).End(xlUp).Row DimgRowAsVariant gRow=.Evaluate("MATCH($G$9&$E$4,Table1!$E1:$E27&Table1!$F1:$F27,0)") DimhRowAsVariant hRow=.Evaluate("MATCH($H$9&$E$4,Table1!$E1:$E27&Table1!$F1:$F27,0)") DimiRowAsVariant iRow=.Evaluate("MATCH($I$9&$E$4,Table1!$E1:$E27&Table1!$F1:$F27,0)") DimjRowAsVariant jRow=.Evaluate("MATCH($J$9&$E$4,Table1!$E1:$E27&Table1!$F1:$F27,0)") Fori=11ToLastRowColumnA IfSheet1.Cells(i,1).Value<>""AndNotIsError(gRow)AndNotIsError(hRow)AndNotIsError(iRow)AndNotIsError(jRow)Then DimclmAsVariant clm=Application.Match(.Range("A"&i),Worksheets("Table1").Range("6:6"),0) IfNotIsError(clm)Then .Cells(i,7)=Worksheets("Table1").Cells(gRow,clm) .Cells(i,8)=Worksheets("Table1").Cells(hRow,clm) .Cells(i,9)=Worksheets("Table1").Cells(iRow,clm) .Cells(i,10)=Worksheets("Table1").Cells(jRow,clm) EndIf EndIf Next EndWith Application.Calculation=xlCalculationAutomatic Application.ScreenUpdating=True Ifthatisstilltooslowthenonewillneedtousevariantarraysandskiploopingtherangesasthisisslow. Share Follow answeredFeb27,2021at19:46 ScottCranerScottCraner 138k99goldbadges4545silverbadges7777bronzebadges 1 Itisfasteronethankyouverymuch.Ifispendevenwholenightanddaycouldnothavebeenmadeasinglelineofthiscode.@ScottCranerThankyouagain. – user14807564 Feb27,2021at19:52 Addacomment  |  YourAnswer ThanksforcontributingananswertoStackOverflow!Pleasebesuretoanswerthequestion.Providedetailsandshareyourresearch!Butavoid…Askingforhelp,clarification,orrespondingtootheranswers.Makingstatementsbasedonopinion;backthemupwithreferencesorpersonalexperience.Tolearnmore,seeourtipsonwritinggreatanswers. Draftsaved Draftdiscarded Signuporlogin SignupusingGoogle SignupusingFacebook SignupusingEmailandPassword Submit Postasaguest Name Email Required,butnevershown PostYourAnswer Discard Byclicking“PostYourAnswer”,youagreetoourtermsofservice,privacypolicyandcookiepolicy TheOverflowBlog CelebratingtheStackExchangesitesthatturnedtenyearsoldinSpring2022  GitHubCopilotishere.Butwhat’stheprice?(Ep.457) FeaturedonMeta Testingnewtrafficmanagementtool Duplicatedvotesarebeingcleanedup Trending:Anewanswersortingoption AskWizardTestResultsandNextSteps Updatedbuttonstylingforvotearrows:currentlyinA/Btesting Related 2087 HowdoIcreateanExcel(.XLSand.XLSX)fileinC#withoutinstallingMicrosoftOffice? 790 HowdoIproperlycleanupExcelinteropobjects? 551 IsthereawaytocrackthepasswordonanExcelVBAProject? 167 ExcelVBAAppstopsspontaneouslywithmessage"Codeexecutionhasbeenhalted" 199 ExcelVBA-exitforloop 605 HowtoavoidusingSelectinExcelVBA 710 HowtouseRegularExpressions(Regex)inMicrosoftExcelbothin-cellandloops HotNetworkQuestions WhereisIBMLotusSymphonytoday? DidVolodymyrZelenskyysaythat"ifsomeoneinUkrainefeelsRussian,theyshouldleaveforRussia"? HowtoachievethisglitchtexteffectinAdobePhotoshop? Howareanygunrestrictionsconstitutional? Doesthe"particleinabox"necessarilyformastandingwave? Howto(safely)permanentlyturnoffnaturalgas? Doesthediscriminantofanirreduciblepolynomialofafixeddegreedeterminethediscriminantofthenumberfielditgenerates? Thecombinatoricsoftheclumsydishwasher WhatcountsasawordforthepurposesofSending? CreatingacampaignthatendswithaTPKbydesign Howtorotate3Darrow Cansiliconecaulkfilla1cmshowergap? Whenwouldonecarryaswordonashoulderwithgripupwards? Whatwordstodescribethethreemonomolecularstructures MidpointcoordinatesfromlineinFieldCalculator(QGIS) Tellingtime:零两分vs零二分 Novelaboutincompetentspacepirates Why"Ihre"andnot"Ihrer"? Istheresuchthingas"administratorroot"? WhatdoestheWesthavetogainbynotallowingRussiatopayitsdebt? Whywouldanationrequireimmigrantstofulfiladecadeofmilitaryservicetogaincitizenship? WhatdoestheOrbofTimeactuallydo? Isthisproofthatmasslessobjectscannotbecharged? DoesonegoodSTRsavingthrowfreethemonsterfrombeingrestrainedbothbyEntangleandEnsnaringstrike? morehotquestions Questionfeed SubscribetoRSS Questionfeed TosubscribetothisRSSfeed,copyandpastethisURLintoyourRSSreader. lang-vb Yourprivacy Byclicking“Acceptallcookies”,youagreeStackExchangecanstorecookiesonyourdeviceanddiscloseinformationinaccordancewithourCookiePolicy. Acceptallcookies Customizesettings