everybody in the comments is crying about 20 seconds and 1 minute. I've been developing a project for my company for the past half a year that is a constant work in progress. it's a combination of metrics and reports and data is constantly getting bigger and bigger. My code went from 25 seconds to minutes, to 45 minutes and lately one hour and 30 minutes. I'm really looking to find out if the application.calculation = manual part is going to do the magic and speed it up. I don't mind waiting few minutes and watching it dance but one hour and 30 minutes is mental at this point. I understand that it would take about 2 weeks worth of manual labor to compile all of it but still. I hope this trick works. Thanks for sharing and uploading
My UserForm was taking 20-30 seconds to finish and add data to the sheet (over 5000 rows). After I applied the code shown in this video to the area of my code that assigns values to the worksheet; my form finishes and updates the sheet in 2-3 seconds! Awesome tip!
you can speed up that for x slope massively with factor 1/(number of rows) by making the goalrange to an data array, change the array and make the changed array to range again. Its also faster to read out of a array instead of a cell a=r (y,x) much faster than a=cells (y,x)! So first grab the whole range to an data array before processing!
Followed instructions, this actually disabled all the subs. My Form was worthless. Cool video though, just not what I need. I’ll figure it out. Keep making these! They’re awesome! Subscribed!
How do you replace copy paste method to (a)=(b) or actual cell referencing for variable ranges of cells? I understand it will be a lot faster but how do i do that? Like for example for copy method you can select range("a1:z" & lastrow) with lastrow being a variable and copy then paste, but if i can also do that using direct referencing then it will be much faster 😀
All, I found something big I think. My macro was taking 10-20 seconds each time to run. I was already using screenupdating which before that the macro never seemed to finish (almost a minute). Even after all above tips, I was stuck at 10-20 seconds. I found that after a few seconds, if I clicked off the Excel workbook onto any other running window on my desktop, getting focus OFF of Excel, it would finish "instantly". This bugged me because I knew something could be done to seriously speed it up. I used the command "application.visible=false" at the start of my main macro in the MODULE code, and then visible=true at the end. Voila! My marco now runs in 1 second! Sure, a little odd that the entire excel spreadsheet window disappears temporarily, but in my case, my macro runs on workbook_open, when you first double click the file, so it's not so bad to have the window "flash" for 1 second off then back when opening, to make the macro run in 1 second rather than see a spinning circle cursor for 10-20 each time you open it. I am thinking there must be yet another similar command to divert excel away from itself, to do the same thing, rather than application.visible=false. For example, I noticed if I have the VBE window open and I was tweaking code, when I would run /continue running the macro from that window, since FOCUS remains OFF the excel window, in some cases it would also run in just one second, and I would be thinking the macro didn't run. I am using application.visible=false then back to true following my startup macro now as the biggest improvement factor yet.
If the activesheet upon opening the workbook is causing the screen to move a lot, you could tell it to change sheets until it's run its course to a boring worksheet. but application.screenupdating = false and then back to true should be coupled with application.calculation=false and then true at the end. That should help a lot.
Hey, Can some one tell me What amount of RAM is used during Your calculations? I have 64 bit windows and64 bit office on a 16 GB RAM pc but only use 4 GB wtf ? Did I do something wrong?
Not sure but you could try File-Options-Advanced-Formulas(just scroll down) and make sure enable multi thread calculations is checked and set to use all processors on this computer. It should be like that by default so unlikely to be the problem I'm afraid :/
After watching 20 youtube videos, 100% did not solve your problem. Let me pour a few cents here : 1) Delete the end cells (Shift+Ctrl+End), you need to delete whole rows & columns - 98% of slow excel problem 2) The others are not much help - disable auto calculation - dis-format especially conditional formatting - Data validation - Pivot table - Macro - Use better and shorter formula
Oh my, thank you so much - had a big macro that included other macros - was taking 50 seconds to complete - now takes around 7 seconds. Amazing.
everybody in the comments is crying about 20 seconds and 1 minute. I've been developing a project for my company for the past half a year that is a constant work in progress. it's a combination of metrics and reports and data is constantly getting bigger and bigger. My code went from 25 seconds to minutes, to 45 minutes and lately one hour and 30 minutes.
I'm really looking to find out if the application.calculation = manual part is going to do the magic and speed it up. I don't mind waiting few minutes and watching it dance but one hour and 30 minutes is mental at this point. I understand that it would take about 2 weeks worth of manual labor to compile all of it but still. I hope this trick works. Thanks for sharing and uploading
My UserForm was taking 20-30 seconds to finish and add data to the sheet (over 5000 rows). After I applied the code shown in this video to the area of my code that assigns values to the worksheet; my form finishes and updates the sheet in 2-3 seconds!
Awesome tip!
Massively speeding up is good!!!! :)
you can speed up that for x slope massively with factor 1/(number of rows) by making the goalrange to an data array, change the array and make the changed array to range again. Its also faster to read out of a array instead of a cell a=r (y,x) much faster than a=cells (y,x)!
So first grab the whole range to an data array before processing!
Followed instructions, this actually disabled all the subs. My Form was worthless. Cool video though, just not what I need. I’ll figure it out. Keep making these! They’re awesome! Subscribed!
My command buttons need to clicked once then again, then again sometimes, that's annoying. I'll try these tips! thank you.
thx for the video, that's great!
How do you replace copy paste method to (a)=(b) or actual cell referencing for variable ranges of cells? I understand it will be a lot faster but how do i do that? Like for example for copy method you can select range("a1:z" & lastrow) with lastrow being a variable and copy then paste, but if i can also do that using direct referencing then it will be much faster 😀
awesome! Thanks
OMG Dan, you wont believe . I was about to post a question to see if you could do a video same topic .
Excellent tips, thanks so much, helped so much.
Awesome, thanks!!
ExcelVbaIsFun timer
السلام عليكم شكرا على الدرس الرائع
Very very nice
Thanks Dan!
I don't have a macro that I can insert the code into. Would really like to make use of these techniques so what can I do now?
You're welcome, Sobolev!!
Dan
Make Excel mad? Lol. Thanks for the video Dan.
All, I found something big I think. My macro was taking 10-20 seconds each time to run. I was already using screenupdating which before that the macro never seemed to finish (almost a minute). Even after all above tips, I was stuck at 10-20 seconds. I found that after a few seconds, if I clicked off the Excel workbook onto any other running window on my desktop, getting focus OFF of Excel, it would finish "instantly". This bugged me because I knew something could be done to seriously speed it up. I used the command "application.visible=false" at the start of my main macro in the MODULE code, and then visible=true at the end. Voila! My marco now runs in 1 second! Sure, a little odd that the entire excel spreadsheet window disappears temporarily, but in my case, my macro runs on workbook_open, when you first double click the file, so it's not so bad to have the window "flash" for 1 second off then back when opening, to make the macro run in 1 second rather than see a spinning circle cursor for 10-20 each time you open it. I am thinking there must be yet another similar command to divert excel away from itself, to do the same thing, rather than application.visible=false. For example, I noticed if I have the VBE window open and I was tweaking code, when I would run /continue running the macro from that window, since FOCUS remains OFF the excel window, in some cases it would also run in just one second, and I would be thinking the macro didn't run. I am using application.visible=false then back to true following my startup macro now as the biggest improvement factor yet.
If the activesheet upon opening the workbook is causing the screen to move a lot, you could tell it to change sheets until it's run its course to a boring worksheet. but application.screenupdating = false and then back to true should be coupled with application.calculation=false and then true at the end. That should help a lot.
thanks Boss i got it now, really cool. thanks
great!
hi Dan how do i get samples of your files?
lol! cool, glad that helps. Dan
Hey, Can some one tell me What amount of RAM is used during Your calculations?
I have 64 bit windows and64 bit office on a 16 GB RAM pc but only use 4 GB wtf ? Did I do something wrong?
Not sure but you could try File-Options-Advanced-Formulas(just scroll down) and make sure enable multi thread calculations is checked and set to use all processors on this computer. It should be like that by default so unlikely to be the problem I'm afraid :/
احلى اشتراك لأحلى فنان
Nice
The dropbox links are in the description above, you may have to click "Show More" button. Dan
i want to download this file, but give me (error 429)
dropbox link to the files is in the description above. Dan
After watching 20 youtube videos, 100% did not solve your problem. Let me pour a few cents here :
1) Delete the end cells (Shift+Ctrl+End), you need to delete whole rows & columns - 98% of slow excel problem
2) The others are not much help
- disable auto calculation
- dis-format especially conditional formatting
- Data validation
- Pivot table
- Macro
- Use better and shorter formula
dropbox link to the files is in the description above. Dan