Paul, you are on fire of late! Some EXTREMELY useful techniques explained very well. I have always been able to tell the level of a developer by what stage they are at in these types of examples. I will be recommending this video to lots of people.
Hi Wyatt, That's actually a great point you make. It's like the evolution of a VBA developer. The begin using Select and activate and evolve through each stage from there.
that pretty funny, i'm learning vba for the last 3 month, and this is precisely the steps i was taking it. Although i've stopped at the array level, i know that i'm miles away, too crude in the knowledge about the rest. This advanced filter, to me , looks like that i have found a treasure chest hidden somewhere.
Saw this video and the next day reworked some macros I wrote for work. Previously if there were more than 20 files you might as well wander down the hall, pour a cup of coffee and when you got back it would be finishing, maybe. In less than a hour of recoding I had it down to 9 seconds! Today I got it under 2 seconds for 70 files.
Paul, you are one of the best overall coders I have found. You think out all the critical issues around a successful project - design, operational speed efficiency, code maintenance, user experience and project construction. It would be a joy to have you on or leading any team I have in the future.
Instead of setting calculation to automatic at the end, it’s best practice to capture the current calc mode at the beginning. And set it back to that at the end. Manual calc may have already been set in Excel, a macro that turns it back on can be an annoyance.
This is amazing. I got suggested your video by youtube. Most VBA videos are too simple for me. I had no idea that turning off calculations would give such a drastic improvement. It cut my execution time from 161 seconds to 1.2 seconds
I think this may have been THE best VBA advice I have ever seen. Working in a large company everyday, sometimes handling hundredthousands of lines of data, I can't wait to implement these advice in my future work. And maybe even rework some of my existing code.
Thanks Paul, Just completed coding to process 2.5 lakhs of records, its taking quite a lot of time, had to split it into 5 sets and run the code. This video provides, clear picture where i can improve my code and speed up the process. Thank you very much...
Hi. i'm in the middle of creating an output sheet with a bunch of copy/paste from different sheets... Most useful youtube recommandation EVER! Thanks for the tips, it'll be very usefull
As someone who loves Macroing :) everything I do in excel and who is totally self taught with the massive help of google, that was inspiring. One of the most helpful things I have seen. Thank you!!
Paul this is probably best Excel video I have ever watched - what you have shown is just amazing and something that will benefit me massively - thanks so much for sharing and keep up the great work!!
I really found these tips super easy to use and they allowed me to improve my spreadsheets performance 380x. Reduced run speed from 45 mins to 8 sec! Woohoo. The best one by far was reading the worksheet data into an array and processing it in memory rather than accessing the sheet. These instructions were so clear and easy to follow. Amazing. I even implemented the module timing in my code to target savings.
I have never tried to put the data in an array and then process it. Very clever. People should also know how much faster Excel 2010 was at running VBA code than 13,16 or 365. If you haven’t upgraded stay away. And if you have a lot of code use a VBA decompiler from time to time. Errors will build up in the P-code and you can clear them out. I don’t know why but it can also speed the macros up. Because magic but I swear it’s true. Great video this should be a “watch first” for learning VBA!
Fantastic overview and breakout of the code! I never realized the cell to cell was so slow and traditional coding was so much faster (Arrays). That element collector trick is also very amazing!
@@Excelmacromastery Only caveat is that whilst developing code, if it crashes and doesn't get to turn screen updating back on, you can find yourself stuck and potentially have to kill the program and lose any unsaved code. Sometimes it's prudent to only include the on/off code after you have solidified your code. My 2 cents worth. :-)
@@andrewlockwood6102 In this case, you can create a macro in your personal.xlsb that turns all of it back on. You then designate that macro as a custom button in your quick access toolbar. Something goes wrong, runtime error or whatever, click the button, everything is turned back on.
@@andrewlockwood6102 it only makes you unable to interact with the normal excel UI but not with the VBEditor Window. So just quickly write a new macro with "application.screenupdating = true" and everything is working fine again
Great explanation and demonstration of how much of an impact different coding will have on the efficiency in getting the processed results. I am new to VBA coding and I am making my first attempt at writing script for an Excel project. I am attempting to write VBA code to accomplish the task of transferring data from 7 individual Excel Workbooks to combine into 1 Main Excel Workbook file. I only need to transfer 3 columns of data from each of the 7 individual workbooks as follows: From: Team A Excel Workbook, Sheet 1, Columns: D, K and T. To: Main Excel Workbook, Sheet 1, Columns: D, K and T. From: Team B Excel Workbook, Sheet 1, Columns: E, L and U. To: Main Excel Workbook, Sheet 1, Columns: E, L and U. From: Team C Excel Workbook, Sheet 1, Columns: F, M and V. To: Main Excel Workbook, Sheet 1, Columns: F, M and V. From: Team D Excel Workbook, Sheet 1, Columns: G, N and W. To: Main Excel Workbook, Sheet 1, Columns: G, N and W. From: Team E Excel Workbook, Sheet 1, Columns: H, O and X. To: Main Excel Workbook, Sheet 1, Columns: H, O and X. From: Team F Excel Workbook, Sheet 1, Columns: I, P and Y. To: Main Excel Workbook, Sheet 1, Columns: I, P and Y. From: Team G Excel Workbook, Sheet 1, Columns: J, Q and Z. To: Main Excel Workbook, Sheet 1, Columns: J, Q and Z. What is the best way to apply the same code efficiency that you demonstrated in this video to this kind of task?
Paul, you've blown me away! I'll be using these techniques to vastly improve the speed of my current Excel projects. Some of my projects take 10 to 15 minutes. This is bonkers! I've been writing the most inefficient code possible.
Thanks Ian. Don't worry we've all been there. The Copy and Paste method is so common place in VBA web examples that when I discovered assign I was sure it couldn't possibly be better than Copy and Paste.
Great stuff! I use some of those techniques myself, the way you explained everything was definitely on point. My only critique would be to have the code comparisons side by side instead of flat out rewriting so viewers can see the difference if they pause the video. Might be helpful to some.
As the only accountant at my last two jobs that knew VBA, it's extremely refreshing to know others are using VBA at this level. I've always referenced your site, I did not know you had a UA-cam channel, I've been on here just going through videos for (Repeating them of course) the last two days. Thank you so much for sharing this knowledge.
Hats off to you sir. Big help. Im running a small engineering calculation. but still there are hundreds of sheets need to be duplicated. these tips improve it by a lot.
Hello Sir.. I have below code in my file.. it takes about 10 seconds to run.. how can we get it work faster..!? Sub do_it() For r = 7 To 18 Range("K" & r & ":L" & r).Value = Range("I" & r & ":J" & r).Value If Cells(r, "G") "" Then Cells(r, "K") = Cells(r, "G") If Cells(r, "H") "" Then Cells(r, "L") = Cells(r, "H") Next r End Sub
Hi, I have two questions: 1) I have an Excel simulation where I have 15'000 clients and want to run 10'000 scenario. At the end I'll produce four matrices (15'000 times 10'000 times 4). It takes about 36 minutes for 1000 scenarios and I already worked with arrays (as good as I could with my limited knowledge). Do you think it is possible to store up to 0.6 billion data records in Excel or do you have a tip what to do because Excel almost breaks down ;-). Should I "outsource" data into CSV-spreadsheets instead? 2) Do you know a good random number generator in VBA. I tried to use the "built in" random number generator but somehow it didn't work with my array code.
Working on a spreadsheet with 1 million rows (Excel 2016 32 bits). Manual calculation mode. Tested it with 2000 rows and it took about 100 sec. When I ran the 1 million rows, even after 14 hours, it has not finished. Don't know whether it was hanging or needed more time. I will try out some of your tips. About 1/2 of what you have shown is an alien language to me. Ha. Not that well versed in VBA. Many many thanks for this video. You made my day!
Glad you enjoyed it. Check out my newest video which is a follow up to this one with more optimization techniques ua-cam.com/video/RNqd89K_bbU/v-deo.html
Probably one of the best VBA help videos I have seen, You explained each part very clearly without over speaking, Thank you. Things I learned from the video (MicroTimer, Resize, populating an Array straight from a range). I use assignment often, but looking forward to using it with Resize now. Favorite part of the Video is jumping to just using Advanced Filter - Sometimes we over code for things that there is already a built-in function for. Thanks again!!!
How would you make an array from multiple tables in the same sheet to store values? context: lets say you want to save some kind of profiles that you can switch / load any other time, like having a table with check-boxes, some checked and some unchecked, and you want to save it to be available to load another time
Thanks a lot mate! I watched this video and two days later I got to apply those tips to a code that was taking around 5s to run. After making the changes it runs instantaneously which is how it was supposed to be. Keep up the good work!
The advantage of using arrays is that beyond being the second fastest, one could write text files (csv) out of the data collected fom the source sheet, parse to databases, etc . Moreover, one could cancatenate values from different columns, perform math operations between column/rows values, etc, all from the dataset in memory. In a nutshell, one could make a lot of things out of the array data in memory as opposed of using the Advanced Filter method which is fast but you don't have the control of the aquired data from the source sheet. Adv. Filter is fast but only transfers data from one sheet to another as it is, then you'd have to process the dataset again if you wanted to do more things with it. I personally have used the array method for the reasons stated above, unless I just want to filter data.
Brilliant, well explained. I've not used vba for years and I was helping someone out with something urgent for their work. I got what they needed sorted out but my process was running so slow. They are giving a demonstration today but it's been annoying me that it's taking around 2.30mins to run through about a thousand records. Now I see its because I am making all these select cut paste onto different worksheets. I will use some of the techniques you so brilliantly explained. So I had to subscribe. Thanks again.
Changing from reading directly from the sheet to loading the range into an array sped the macro up from 1 min 15 sec to only 1 second. Couldn't almost believe it! Amazing! :D
A note on Paul's final comment about sampling these methods on a small set of data first: Some of these methods will only give you gains as you scale up. For example, in a table with 1800 rows, adding the TurnOffStuff and TurnOnStuff functions added 15ms to my run time. Results from a very small sample size *may not be indicative* of performance effects at a larger scale.
Really good. VBA code has an astonishing ability to yield to all sorts of improvements if you just nudge it a bit. But you gotta know where to look. One thing I will say is that you might do yourself a disservice by removing all the scaffolding, which is what you’ve done with the last step. I guarantee 100% that in a job like this, at some point you are going to want to do something a bit special with the records found. Three days after removing the loop, you’re gonna be putting it back in. The loop is the scaffolding. Don’t chuck it out lightly, or it’s Step 1: Optimise. Step 2: Deoptimise.
Thank you sooo much. You have taken my code and shortened it's run time by quite a bit :) I still need to learn and understand the Advanced Filter a little better, but you have helped improve my workload significantly. I truly appreciate it :)
Hi, I'm a novice excel macro user and I find this very interesting. HOWEVER, I'm stumbling into problems trying to apply your technique which I think is very useful in learning how to optimize use of macros thru VBA. My question surrounds the microtimer formula you applied to test the speed of the macro. I want to do something similar for a macro I am working on where I format a query dump which 1) adds standard header and footer data; 2) renames certain column headers from ugly program field names to field names that are in english (e.g. instead of a field named ''wk_tm_dt" rename it to "date", etc. etc); 3) removes irrelevant columns with no meaningful data, 4) formats the date column to be read as the "day of the week", mm/dd/yyyy", & 5) formats number columns to x,xxx.xx format. The macro takes a significant amount of time and I see opportunites to speed up the macro based on your interesting points noted. For learning purposes, I want to replicate what you've done in order to measure the speed improvement. For my macro code, I added the start time at the beginning of my macro as how you wrote it (Dim dTime As Double *space* dTime = MicroTimer) & at the end of my macro code (Dbug.Print "Time is" & (MicroTimer - dTime) * 1000. However when I run my query, in VBA, it indicates "Time is= 0" What did I do incorrectly and how do I get my macro to show the time it took to run my macro like how you did? My macro takes around 10 seconds and I want to get that time down. Thanks for sharing the possibilities. All these years and all these wasted hours I could have saved had I learned your mode of thinking sooner.
Paul, you are always great teacher, i spent lots of times for copy and pasta data with criteria before but now this is amazing. Awesome. Thank you man.
Wow, one of the best VBA videos I've ever seen. Very helpful and everything explained very straight forward! I like that I wouldn't necessarily use everything, but the step by step way you explained each thing you did makes it easy to apply where it makes the most sense. Well done!
Didn't comment first time watching this, advanced filter method is my "go-to" for any code that needs a copy paste. On average, has mitigated 96% waste generation (time) based on original process. Thanks for sharing :)
Hi Paul, this is absolutely fantastic - I have watched it several times and made copious notes. I hope to be able to implement it in my personal stock recorder program John
Hi John, Great to hear from you as always. Thanks for the feedback. I will be releasing plenty of new videos in the coming weeks so key an eye out for them. Regards Paul
This is my first time seeing one of your videos and I'm impressed! Your explanation made complete sense, at least while I was watching and listening to you. I'm going to have to watch it a couple more times to get it to stay in my head, LOL! But your directions are clear, pretty easy to understand and the visuals going right along with your narration is VERY helpful to me! Thanks!
This is amazing. I wish I had found your site some time ago. I do have a couple of applications that I want to update. I am anxious to see how fast I can make them run. Thanks for this video.
Hello Paul, you write data from an array to cells cell-for-cell. This is the slowest way! Use the same way like reading data, the whole range. You do not need a loop. (11:25)
Great video! I was quite hesitant on using arrays in the past as I was considering the additional resources it would take considering the variant datatype. But that advanced filter is a game changer and is kinda new to me. I would love to try it soooon.
Hi Paul, Your videos and the way in which you explain them is to the highest level! Thanks for making our lives easier in the deep sea of code which we all love (some may hate I am sure haha).
Man thanks you very much, the runing time from my current project drop down from 2 and a half minutes to 3 seconds and I think it can go a lot lower. Thanks from Colombia 👍
Let me expres my gratitude in VBA language: shInput.Range("Beginner").Resize (i, 1). Value = PaulOutput (i,1).Value 'Way to Perfection Thank you for your cristal clear lessons !
Great video. Even if intuitively, in the given scenario, filtering is THE WAY to go. if you faced this problem without knowing how to use VBA, you'd wanna use a filter (simple or advanced - up to the user), so why would you want VBA to do it the hard way and loop through every row? This is not a question to you, Paul, but to anyone who would want to code this type of scenario in any way other than: 1. simple filter, 2. advanced filter or 3. array (yes, i know this involves looping, but looping through arrays is WAY FASTER than cells). Still, i can believe there are people out there who would loop through cells, and i also agree, that there is a lot of unnecessary usage of 'Activate' and 'Select'. So thanks once again for explaining this very important topic in a concise and easy to follow video. I'm not a VBA expert by any stretch of imagination, but i have been using it for a while, and have seen many websites/tutorials/videos and i have to say, yours are definitely among the best :)
In my experience I think the fastest way to copy a data table in from a sheet is using a filter is ADO recordset toghether with the CopyFromRecordset command. You can filter, order a table and perhaps you can do join query building a proper connection data source string...
Working on my nfl model for dfs games. (just a hobbiest) my old version simulated 1 game of the full slate in 181937/1000. After finding this and learning about arrays, my new code does the full slate and sims each game 100000 times in 585010/1000 seconds. Around a 3010X speed improvement. Learned a crap ton from you and have started using gpt as a coding better. I've done in one week what I couldn't do in 5 years. Cheers!
I've used arrays extensively, but haven't fiddled with Advanced Filter. This video has me wondering where I can change my code to use this cool feature!
I typically try to use a "sort" to group my search data then use "find" to identify the first row then a second find (xl previous) to identify the last row. With these two location parameters I can then assign value between the first row and last; that my results are located in. I know this is significantly faster than trying to loop through each available value. I haven't considered timing it compared to using an array.
it may be what the advance filter does internally under the hood - it would be interesting to actually see the timing comparison between the advanced filter and your method to be sure which one is really faster
Wonderful method comparison! Thank you, Paul! I use advanced filter a lot in my code but never measured the time difference when compared with the other methods.
Beautiful. Most code I write is one off to alter 27 identical spreadsheets (businesses) but there is a big data filter sub I have which I use alot but is slow. I can apply all these techniques. Thankyou so much.
Very nice video I learned some of these techniques after years of learning VBA Thank you for sharing Can you make a video on getting three or more tablets from Oracle database. Then make relationship in power query or excel and then show the results in normal excel spreadsheet. Basically this question is about Primavera database
love from first video.. This is impressive and extremely useful. You are outstanding in both, knowledge and ease of explaining complicated stuff. Thank you for sharing. Would love to see more of practical videos like this from you.
Great stuff. At the end of your code you should take the objects you SET and SET them to equal NOTHING. Excel is notorious for not cleaning itself up even when code has completed execution. It's just part of good housekeeping.
Thanks Tim. Objects will be deleted when they go out of Scope. I know it's good practice to set to nothing but I don't know if it makes much difference.
Paul, you are on fire of late! Some EXTREMELY useful techniques explained very well. I have always been able to tell the level of a developer by what stage they are at in these types of examples. I will be recommending this video to lots of people.
Hi Wyatt,
That's actually a great point you make. It's like the evolution of a VBA developer. The begin using Select and activate and evolve through each stage from there.
@@Excelmacromastery hi, actualy I am looking your other video that explains it .. thanks
Please provide your inputs
Check this too
ua-cam.com/users/ExcelAutomationByMudit
that pretty funny, i'm learning vba for the last 3 month, and this is precisely the steps i was taking it. Although i've stopped at the array level, i know that i'm miles away, too crude in the knowledge about the rest.
This advanced filter, to me , looks like that i have found a treasure chest hidden somewhere.
I thank God because people like you exist.
Saw this video and the next day reworked some macros I wrote for work. Previously if there were more than 20 files you might as well wander down the hall, pour a cup of coffee and when you got back it would be finishing, maybe. In less than a hour of recoding I had it down to 9 seconds! Today I got it under 2 seconds for 70 files.
I've been a very advanced VBA programmer for a lot of years, but still learned something new. Thanks.
That's great to hear Tom.
Check this too
ua-cam.com/users/ExcelAutomationByMudit
This is absolutely amazing. I went from a run time of 6 minutes to 14 seconds! Thank you so much!
Thanks Peter!
Paul, you are one of the best overall coders I have found. You think out all the critical issues around a successful project - design, operational speed efficiency, code maintenance, user experience and project construction. It would be a joy to have you on or leading any team I have in the future.
One of the best and most useful Excel Videos found on the entire internet. Well Done and a big THANK YOU.
Instead of setting calculation to automatic at the end, it’s best practice to capture the current calc mode at the beginning. And set it back to that at the end. Manual calc may have already been set in Excel, a macro that turns it back on can be an annoyance.
Agree with brendan
I really wish I had watched this two years ago!! It would have saved me a lot of time learning some of these techniques the hard way!!
I wish I had watched myself 10 years ago😛
This is amazing. I got suggested your video by youtube. Most VBA videos are too simple for me.
I had no idea that turning off calculations would give such a drastic improvement. It cut my execution time from 161 seconds to 1.2 seconds
That's great to hear:-)
I think this may have been THE best VBA advice I have ever seen. Working in a large company everyday, sometimes handling hundredthousands of lines of data, I can't wait to implement these advice in my future work. And maybe even rework some of my existing code.
Glad you like it.
Thanks Paul, Just completed coding to process 2.5 lakhs of records, its taking quite a lot of time, had to split it into 5 sets and run the code.
This video provides, clear picture where i can improve my code and speed up the process.
Thank you very much...
Hi. i'm in the middle of creating an output sheet with a bunch of copy/paste from different sheets... Most useful youtube recommandation EVER! Thanks for the tips, it'll be very usefull
As someone who loves Macroing :) everything I do in excel and who is totally self taught with the massive help of google, that was inspiring. One of the most helpful things I have seen.
Thank you!!
This guy definitely knows his stuff! I am impressed.
A big thanks from Germany for this very useful und powerful way to code, people like you make this world better!
Thanks Philipp
Paul this is probably best Excel video I have ever watched - what you have shown is just amazing and something that will benefit me massively - thanks so much for sharing and keep up the great work!!
Thanks Brad. Glad you have got so much use from this video.
I write lots of VBA code and I learned new tricks. I never used the advanced filter before. Thanks for creating this overview.
Glad it was helpful!
I really found these tips super easy to use and they allowed me to improve my spreadsheets performance 380x. Reduced run speed from 45 mins to 8 sec! Woohoo. The best one by far was reading the worksheet data into an array and processing it in memory rather than accessing the sheet. These instructions were so clear and easy to follow. Amazing. I even implemented the module timing in my code to target savings.
I have never tried to put the data in an array and then process it. Very clever.
People should also know how much faster Excel 2010 was at running VBA code than 13,16 or 365. If you haven’t upgraded stay away.
And if you have a lot of code use a VBA decompiler from time to time. Errors will build up in the P-code and you can clear them out. I don’t know why but it can also speed the macros up. Because magic but I swear it’s true.
Great video this should be a “watch first” for learning VBA!
and what about excel 2021?
Fantastic overview and breakout of the code! I never realized the cell to cell was so slow and traditional coding was so much faster (Arrays). That element collector trick is also very amazing!
This is the double slit experiment for Excel. Thank you for breaking this down.
This is one of the most genuinely helpful contents I have ever come across.
application.screenupdating = false
is a must for every VBA user.
That's true.
@@Excelmacromastery Only caveat is that whilst developing code, if it crashes and doesn't get to turn screen updating back on, you can find yourself stuck and potentially have to kill the program and lose any unsaved code. Sometimes it's prudent to only include the on/off code after you have solidified your code. My 2 cents worth. :-)
@@andrewlockwood6102 In this case, you can create a macro in your personal.xlsb that turns all of it back on. You then designate that macro as a custom button in your quick access toolbar. Something goes wrong, runtime error or whatever, click the button, everything is turned back on.
@@andrewlockwood6102 it only makes you unable to interact with the normal excel UI but not with the VBEditor Window. So just quickly write a new macro with "application.screenupdating = true" and everything is working fine again
Best practice is always to implement error handling and Set back to defaults there
Great explanation and demonstration of how much of an impact different coding will have on the efficiency in getting the processed results. I am new to VBA coding and I am making my first attempt at writing script for an Excel project. I am attempting to write VBA code to accomplish the task of transferring data from 7 individual Excel Workbooks to combine into 1 Main Excel Workbook file. I only need to transfer 3 columns of data from each of the 7 individual workbooks as follows:
From: Team A Excel Workbook, Sheet 1, Columns: D, K and T. To: Main Excel Workbook, Sheet 1, Columns: D, K and T.
From: Team B Excel Workbook, Sheet 1, Columns: E, L and U. To: Main Excel Workbook, Sheet 1, Columns: E, L and U.
From: Team C Excel Workbook, Sheet 1, Columns: F, M and V. To: Main Excel Workbook, Sheet 1, Columns: F, M and V.
From: Team D Excel Workbook, Sheet 1, Columns: G, N and W. To: Main Excel Workbook, Sheet 1, Columns: G, N and W.
From: Team E Excel Workbook, Sheet 1, Columns: H, O and X. To: Main Excel Workbook, Sheet 1, Columns: H, O and X.
From: Team F Excel Workbook, Sheet 1, Columns: I, P and Y. To: Main Excel Workbook, Sheet 1, Columns: I, P and Y.
From: Team G Excel Workbook, Sheet 1, Columns: J, Q and Z. To: Main Excel Workbook, Sheet 1, Columns: J, Q and Z.
What is the best way to apply the same code efficiency that you demonstrated in this video to this kind of task?
Paul, you've blown me away! I'll be using these techniques to vastly improve the speed of my current Excel projects. Some of my projects take 10 to 15 minutes. This is bonkers! I've been writing the most inefficient code possible.
Thanks Ian. Don't worry we've all been there. The Copy and Paste method is so common place in VBA web examples that when I discovered assign I was sure it couldn't possibly be better than Copy and Paste.
Much appreciated. My excel calculation time has dropped from 20 mins to 5mins. It saved lot of time of my life. Thank you so much.
Glad to help Deepak
Man, this is the BEST VIDEO ON VBA in youtube! Thanks a lot.
Great stuff! I use some of those techniques myself, the way you explained everything was definitely on point. My only critique would be to have the code comparisons side by side instead of flat out rewriting so viewers can see the difference if they pause the video. Might be helpful to some.
Thanks Winter. Glad you enjoyed it.
As the only accountant at my last two jobs that knew VBA, it's extremely refreshing to know others are using VBA at this level.
I've always referenced your site, I did not know you had a UA-cam channel, I've been on here just going through videos for (Repeating them of course) the last two days.
Thank you so much for sharing this knowledge.
You're welcome. Glad you like them.
I program in VBA for over 10 years and I have learned again some things. Thank for this video very clear… even for a french with poor english
Glad you like it Sacha.
Hats off to you sir. Big help. Im running a small engineering calculation. but still there are hundreds of sheets need to be duplicated. these tips improve it by a lot.
Hi everyone!
This video has some great practical techniques to help you speed up your code.
Please add any comments below.
Thank you.
thank you, Mr Paul! I'm going to implement it in my team. I wish you all the best in your career!
Hello Sir.. I have below code in my file.. it takes about 10 seconds to run.. how can we get it work faster..!?
Sub do_it()
For r = 7 To 18
Range("K" & r & ":L" & r).Value = Range("I" & r & ":J" & r).Value
If Cells(r, "G") "" Then Cells(r, "K") = Cells(r, "G")
If Cells(r, "H") "" Then Cells(r, "L") = Cells(r, "H")
Next r
End Sub
Hi, I have two questions: 1) I have an Excel simulation where I have 15'000 clients and want to run 10'000 scenario. At the end I'll produce four matrices (15'000 times 10'000 times 4). It takes about 36 minutes for 1000 scenarios and I already worked with arrays (as good as I could with my limited knowledge). Do you think it is possible to store up to 0.6 billion data records in Excel or do you have a tip what to do because Excel almost breaks down ;-). Should I "outsource" data into CSV-spreadsheets instead? 2) Do you know a good random number generator in VBA. I tried to use the "built in" random number generator but somehow it didn't work with my array code.
Read that Excel 2016 32 bits has a file size problem running VBA when it is greater than 50 Mb. True? If so, why? 50 Mb is not that big.
Very nice video...
Please provide your inputs
ua-cam.com/users/ExcelAutomationByMudit
Best VBA tutorials I’ve seen so far!
Thanks for your comment Mei
Thanks for this tutorial. Is a MUST for all 'amateur' programmers like me. Excellent and Thanks once again Regards. Martin ( South Africa)
Working on a spreadsheet with 1 million rows (Excel 2016 32 bits). Manual calculation mode. Tested it with 2000 rows and it took about 100 sec. When I ran the 1 million rows, even after 14 hours, it has not finished. Don't know whether it was hanging or needed more time. I will try out some of your tips. About 1/2 of what you have shown is an alien language to me. Ha. Not that well versed in VBA. Many many thanks for this video. You made my day!
Glad you enjoyed it. Check out my newest video which is a follow up to this one with more optimization techniques ua-cam.com/video/RNqd89K_bbU/v-deo.html
That was impressive. Just turning off the calculations and screen updates saves a considerable amount of time.
Thanks Tony
Wow! What an ending! AdvancedFilter! This is tutorial is breathless action movie! Thank You Sir. Lord.
You're welcome.
Probably one of the best VBA help videos I have seen, You explained each part very clearly without over speaking, Thank you.
Things I learned from the video (MicroTimer, Resize, populating an Array straight from a range). I use assignment often, but looking forward to using it with Resize now.
Favorite part of the Video is jumping to just using Advanced Filter - Sometimes we over code for things that there is already a built-in function for. Thanks again!!!
Thanks Mark. That is high praise indeed. I'm glad you got so much from the video.
I feel happy that I use these techniques all the time when vba calculation time matters on some projects.
Wow, I literally ran my VBA exactly how you said beginners do but that's only because that's how the record macro feature told me to. Very helpful!
Thanks Nick. The macro recorder code is very different than real-world code. It leads to a lot of confusion.
How would you make an array from multiple tables in the same sheet to store values? context: lets say you want to save some kind of profiles that you can switch / load any other time, like having a table with check-boxes, some checked and some unchecked, and you want to save it to be available to load another time
The array instead of range tip! It's so obvious but I had never thought of it. Thank you sir!
Glad to help
Thanks a lot mate! I watched this video and two days later I got to apply those tips to a code that was taking around 5s to run. After making the changes it runs instantaneously which is how it was supposed to be. Keep up the good work!
The advantage of using arrays is that beyond being the second fastest, one could write text files (csv) out of the data collected fom the source sheet, parse to databases, etc . Moreover, one could cancatenate values from different columns, perform math operations between column/rows values, etc, all from the dataset in memory. In a nutshell, one could make a lot of things out of the array data in memory as opposed of using the Advanced Filter method which is fast but you don't have the control of the aquired data from the source sheet. Adv. Filter is fast but only transfers data from one sheet to another as it is, then you'd have to process the dataset again if you wanted to do more things with it. I personally have used the array method for the reasons stated above, unless I just want to filter data.
AWESOME!!!
That's the point of macros - speed!
I am happy there are ppl like you
Glad to help
Brilliant, well explained. I've not used vba for years and I was helping someone out with something urgent for their work. I got what they needed sorted out but my process was running so slow. They are giving a demonstration today but it's been annoying me that it's taking around 2.30mins to run through about a thousand records. Now I see its because I am making all these select cut paste onto different worksheets. I will use some of the techniques you so brilliantly explained. So I had to subscribe. Thanks again.
Thanks for the feedback Tony.
OMG this is an awesome video. All the stuff I do at my work will be so much faster.
Thanks John.
You are the best VBA teacher of me, many thanks
What a huge smart education. You wake me up for VBA.
Glad to help Andres
Changing from reading directly from the sheet to loading the range into an array sped the macro up from 1 min 15 sec to only 1 second. Couldn't almost believe it! Amazing! :D
Nice work!
A note on Paul's final comment about sampling these methods on a small set of data first:
Some of these methods will only give you gains as you scale up. For example, in a table with 1800 rows, adding the TurnOffStuff and TurnOnStuff functions added 15ms to my run time.
Results from a very small sample size *may not be indicative* of performance effects at a larger scale.
Really good. VBA code has an astonishing ability to yield to all sorts of improvements if you just nudge it a bit. But you gotta know where to look. One thing I will say is that you might do yourself a disservice by removing all the scaffolding, which is what you’ve done with the last step. I guarantee 100% that in a job like this, at some point you are going to want to do something a bit special with the records found. Three days after removing the loop, you’re gonna be putting it back in. The loop is the scaffolding. Don’t chuck it out lightly, or it’s Step 1: Optimise. Step 2: Deoptimise.
Thanks Albert. Glad you like it. It always depends on the requirements. Some situations the loop is better and for some the Advanced Filter.
you say that it is better to use the loop then? sorry, english is not my native language.. i didnt understand quiet well
Thank you sooo much. You have taken my code and shortened it's run time by quite a bit :)
I still need to learn and understand the Advanced Filter a little better, but you have helped improve my workload significantly.
I truly appreciate it :)
Thanks Zach. Glad to help.
Hi, I'm a novice excel macro user and I find this very interesting. HOWEVER, I'm stumbling into problems trying to apply your technique which I think is very useful in learning how to optimize use of macros thru VBA. My question surrounds the microtimer formula you applied to test the speed of the macro. I want to do something similar for a macro I am working on where I format a query dump which 1) adds standard header and footer data; 2) renames certain column headers from ugly program field names to field names that are in english (e.g. instead of a field named ''wk_tm_dt" rename it to "date", etc. etc); 3) removes irrelevant columns with no meaningful data, 4) formats the date column to be read as the "day of the week", mm/dd/yyyy", & 5) formats number columns to x,xxx.xx format. The macro takes a significant amount of time and I see opportunites to speed up the macro based on your interesting points noted. For learning purposes, I want to replicate what you've done in order to measure the speed improvement. For my macro code, I added the start time at the beginning of my macro as how you wrote it (Dim dTime As Double *space* dTime = MicroTimer) & at the end of my macro code (Dbug.Print "Time is" & (MicroTimer - dTime) * 1000.
However when I run my query, in VBA, it indicates "Time is= 0" What did I do incorrectly and how do I get my macro to show the time it took to run my macro like how you did? My macro takes around 10 seconds and I want to get that time down. Thanks for sharing the possibilities. All these years and all these wasted hours I could have saved had I learned your mode of thinking sooner.
Did you ever find the answer to this? Same thing is happening with me
Clearly, the guidelines have improved the speed of my code... excellent & simple techniques... thanks
You're welcome Shohan
Paul, you are always great teacher, i spent lots of times for copy and pasta data with criteria before but now this is amazing. Awesome. Thank you man.
I am speechless. This is wonderful stuff. Thank you for sharing. God bless!
Wow, one of the best VBA videos I've ever seen. Very helpful and everything explained very straight forward! I like that I wouldn't necessarily use everything, but the step by step way you explained each thing you did makes it easy to apply where it makes the most sense. Well done!
Thanks Matthew
Didn't comment first time watching this, advanced filter method is my "go-to" for any code that needs a copy paste. On average, has mitigated 96% waste generation (time) based on original process. Thanks for sharing :)
As usual, thanks Paul. While I use the web a lot for examples, you are the only one I keep track of. Love your stuff.
Appreciate the feedback Alphonso.
Great work and Superb method to explain in fluent and descent manner,Love you .Salute for attitude and making it available for all.Thanks
thanks very much Pradeep
Beautifully done, thank you! One suggestion: best to trap errors to turn stuff back on, otherwise can be an unpleasant surprise. Thanks again!
That's true Jeff. When the code is live you definitely need that.
Your live explanation is precious for me.
nothing new for me, but i like how consistently you explain)
good job, definitely.
Thanks Dimon.
Hi Paul, this is absolutely fantastic - I have watched it several times and made copious notes. I hope to be able to implement it in my personal stock recorder program
John
Hi John,
Great to hear from you as always. Thanks for the feedback. I will be releasing plenty of new videos in the coming weeks so key an eye out for them.
Regards
Paul
A major work !
Very helpful and easy to integrate in all VBA code.
Thank you so much
No problem Jerome.
This is my first time seeing one of your videos and I'm impressed! Your explanation made complete sense, at least while I was watching and listening to you. I'm going to have to watch it a couple more times to get it to stay in my head, LOL! But your directions are clear, pretty easy to understand and the visuals going right along with your narration is VERY helpful to me! Thanks!
Thanks for the feedback Jenny. I'm glad you got so much value from the video.
Assignment - *jaw drops and thinks about all the good times that i've wasted copying and pasting*
😅😅
This is amazing. I wish I had found your site some time ago. I do have a couple of applications that I want to update. I am anxious to see how fast I can make them run. Thanks for this video.
Here's a fan from Taiwan!! Thanks for this useful video, I learned a lot.
Hello Paul, you write data from an array to cells cell-for-cell. This is the slowest way! Use the same way like reading data, the whole range. You do not need a loop. (11:25)
I was aware of some of the tricks, but not really aware of the really big ones. Thank you! Well done.
You're welcome.
Wow... Nice... Advanced filters blew my mind! Thank you!
Wow... Something everyone should know if you are using VBA
Great video! I was quite hesitant on using arrays in the past as I was considering the additional resources it would take considering the variant datatype. But that advanced filter is a game changer and is kinda new to me. I would love to try it soooon.
Thanks Arsenio
This is an awesome improvements. I did not know there were so many ways to speed up the code! Thanks for sharing these tips
Outstanding! I have been doing it the slow way for years.
Hi Paul,
Your videos and the way in which you explain them is to the highest level! Thanks for making our lives easier in the deep sea of code which we all love (some may hate I am sure haha).
Didn't know about currentregion till I watched your video. You have helped alot and I wished I had knew about this alot more sooner. Thanks man
Thanks Rajasilan. I hope this video is useful for you.
Awesome video been using vba for 6 years and never realised you could do this stuff
That's great to hear. Glad to be making these techniques available to everyone.
Man thanks you very much, the runing time from my current project drop down from 2 and a half minutes to 3 seconds and I think it can go a lot lower. Thanks from Colombia 👍
Nice work Jair. Glad the video helped you.
Let me expres my gratitude in VBA language:
shInput.Range("Beginner").Resize (i, 1). Value =
PaulOutput (i,1).Value 'Way to Perfection
Thank you for your cristal clear lessons !
These are very useful videos for me. I still want to know more about excel vba array programming.
Great video. Even if intuitively, in the given scenario, filtering is THE WAY to go. if you faced this problem without knowing how to use VBA, you'd wanna use a filter (simple or advanced - up to the user), so why would you want VBA to do it the hard way and loop through every row? This is not a question to you, Paul, but to anyone who would want to code this type of scenario in any way other than: 1. simple filter, 2. advanced filter or 3. array (yes, i know this involves looping, but looping through arrays is WAY FASTER than cells). Still, i can believe there are people out there who would loop through cells, and i also agree, that there is a lot of unnecessary usage of 'Activate' and 'Select'. So thanks once again for explaining this very important topic in a concise and easy to follow video. I'm not a VBA expert by any stretch of imagination, but i have been using it for a while, and have seen many websites/tutorials/videos and i have to say, yours are definitely among the best :)
Thanks - Glad you liked it.
wow !! The Awesome techniques I got today from your channel, Thank you very much for this great knowledge !!
In my experience I think the fastest way to copy a data table in from a sheet is using a filter is ADO recordset toghether with the CopyFromRecordset command. You can filter, order a table and perhaps you can do join query building a proper connection data source string...
Thank you, I tried the first two steps, and increase 10 fold of the speed of my worksheet. Thank you for sharing!
That's great to hear Kai. Thanks for the feedback.
Working on my nfl model for dfs games. (just a hobbiest) my old version simulated 1 game of the full slate in 181937/1000. After finding this and learning about arrays, my new code does the full slate and sims each game 100000 times in 585010/1000 seconds. Around a 3010X speed improvement. Learned a crap ton from you and have started using gpt as a coding better. I've done in one week what I couldn't do in 5 years. Cheers!
Thanks for the feedback. Glad you had such an improvement.
Perfect. Excellent. Thanks a lot to take the time to teach us how to become our codes faster. Greetings from Venezuela. New Follower.
I've used arrays extensively, but haven't fiddled with Advanced Filter. This video has me wondering where I can change my code to use this cool feature!
I typically try to use a "sort" to group my search data then use "find" to identify the first row then a second find (xl previous) to identify the last row. With these two location parameters I can then assign value between the first row and last; that my results are located in. I know this is significantly faster than trying to loop through each available value. I haven't considered timing it compared to using an array.
it may be what the advance filter does internally under the hood - it would be interesting to actually see the timing comparison between the advanced filter and your method to be sure which one is really faster
Wonderful method comparison! Thank you, Paul! I use advanced filter a lot in my code but never measured the time difference when compared with the other methods.
Thanks Celia. Glad you like it.
Thanks Paul. This is the best video I've ever see in learning VBA for mastery. Thanks, i will watch all your 13 videos.
Thanks for your support Blake.
Holy shit! That is a significant time saving. Why didn't I watch this video years ago?
Beautiful. Most code I write is one off to alter 27 identical spreadsheets (businesses) but there is a big data filter sub I have which I use alot but is slow. I can apply all these techniques. Thankyou so much.
You're welcome.
Very nice video
I learned some of these techniques after years of learning VBA
Thank you for sharing
Can you make a video on getting three or more tablets from Oracle database. Then make relationship in power query or excel and then show the results in normal excel spreadsheet. Basically this question is about Primavera database
Thanks for this - I look forward to applying advance filter on current project.
love from first video.. This is impressive and extremely useful.
You are outstanding in both, knowledge and ease of explaining complicated stuff.
Thank you for sharing. Would love to see more of practical videos like this from you.
You're Wecome Jawali.
Great stuff. At the end of your code you should take the objects you SET and SET them to equal NOTHING. Excel is notorious for not cleaning itself up even when code has completed execution. It's just part of good housekeeping.
Thanks Tim.
Objects will be deleted when they go out of Scope. I know it's good practice to set to nothing but I don't know if it makes much difference.