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.
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
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.
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.
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.
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!!
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
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...
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.
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!
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.
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?
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.
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 TH-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.
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!
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 th-cam.com/video/RNqd89K_bbU/w-d-xo.html
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.
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.
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!
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.
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
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.
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 :)
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!!!
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.
@@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
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 :)
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.
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!
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!
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
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!
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.
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.
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 !
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!
hi Paul, I have been following along with all of your videos and level of understanding has been like x1000 level up. Can you make a video to explain about DoEvent and Application.OnKey in VBA. Even though there are few resources for this particular topics, they don't really explain in the way that you do!
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.
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)
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 👍
Hi Paul, thanks for this video I learnt some new stuff :) Just one thing I'm missing is when you use the advanced filter where exactly do you set the filter on the name "Imani"? Never mind, watched it again :)
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.
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).
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.
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...
Wishing I'd another day or two off on furlough to binge-watch your vids but I'm up for work in 6 hours. Just wanted to say that most of us would have stopped when we got the report running in 1.5 secs...the rest is just arcane wizardry and I have a feeling that I'll be reworking a LOT of my older code in the next week or two!
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 :)
Thank you for the great video. I will now search for videos about advanced filters. With this technique I could solve the runtime problem with my program.
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
th-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.
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 thank God because people like you exist.
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
th-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.
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:-)
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
th-cam.com/users/ExcelAutomationByMudit
One of the best and most useful Excel Videos found on the entire internet. Well Done and a big THANK YOU.
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.
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
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!!
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
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😛
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...
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.
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!
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
Thanks for this tutorial. Is a MUST for all 'amateur' programmers like me. Excellent and Thanks once again Regards. Martin ( South Africa)
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!
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 am speechless. This is wonderful stuff. Thank you for sharing. God bless!
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.
This is one of the most genuinely helpful contents I have ever come across.
This is the double slit experiment for Excel. Thank you for breaking this down.
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?
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.
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 TH-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 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?
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 th-cam.com/video/RNqd89K_bbU/w-d-xo.html
Wow... Nice... Advanced filters blew my mind! Thank you!
Man, this is the BEST VIDEO ON VBA in youtube! Thanks a lot.
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.
The array instead of range tip! It's so obvious but I had never thought of it. Thank you sir!
Glad to help
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.
Your live explanation is precious for me.
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
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
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.
wow !! The Awesome techniques I got today from your channel, Thank you very much for this great knowledge !!
OMG this is an awesome video. All the stuff I do at my work will be so much faster.
Thanks John.
AWESOME!!!
That's the point of macros - speed!
I am happy there are ppl like you
Glad to help
Best VBA tutorials I’ve seen so far!
Thanks for your comment Mei
Wow! What an ending! AdvancedFilter! This is tutorial is breathless action movie! Thank You Sir. Lord.
You're welcome.
Perfect. Excellent. Thanks a lot to take the time to teach us how to become our codes faster. Greetings from Venezuela. New Follower.
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!
You are the best VBA teacher of me, many thanks
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.
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!
Thanks for this - I look forward to applying advance filter on current project.
That was impressive. Just turning off the calculations and screen updates saves a considerable amount of time.
Thanks Tony
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
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.
Clearly, the guidelines have improved the speed of my code... excellent & simple techniques... thanks
You're welcome Shohan
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.
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.
A major work !
Very helpful and easy to integrate in all VBA code.
Thank you so much
No problem Jerome.
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.
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
These are very useful videos for me. I still want to know more about excel vba array programming.
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 :)
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.
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
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.
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
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!
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
I love this advanced filter trick! Thanks for sharing!
Glad you liked it!
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.
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.
Great VBA helpful video tips from you sir Paul.
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 !
I feel happy that I use these techniques all the time when vba calculation time matters on some projects.
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.
You are just Great Sir with immense knowledge
hi Paul,
I have been following along with all of your videos and level of understanding has been like x1000 level up. Can you make a video to explain about DoEvent and Application.OnKey in VBA. Even though there are few resources for this particular topics, they don't really explain in the way that you do!
nothing new for me, but i like how consistently you explain)
good job, definitely.
Thanks Dimon.
Extremely useful techniques and Great video...can you please also show us how to make slicers faster.
This is an awesome improvements. I did not know there were so many ways to speed up the code! Thanks for sharing these tips
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.
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.
What a huge smart education. You wake me up for VBA.
Glad to help Andres
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)
Thank you so much for your great videos! You are great at explaining. Im learning a lot
I was aware of some of the tricks, but not really aware of the really big ones. Thank you! Well done.
You're welcome.
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.
Hi Paul, thanks for this video I learnt some new stuff :)
Just one thing I'm missing is when you use the advanced filter where exactly do you set the filter on the name "Imani"?
Never mind, watched it again :)
That is some very valuable techniques! Will definitely be using them to write/REwrite macros. Thanks! 😊
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.
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).
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
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...
This is really cool video for me as a VBA beginner
Wishing I'd another day or two off on furlough to binge-watch your vids but I'm up for work in 6 hours. Just wanted to say that most of us would have stopped when we got the report running in 1.5 secs...the rest is just arcane wizardry and I have a feeling that I'll be reworking a LOT of my older code in the next week or two!
Thanks. Glad you liked it.
Outstanding! I have been doing it the slow way for years.
Here's a fan from Taiwan!! Thanks for this useful video, I learned a lot.
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.
Thank you for the great video. I will now search for videos about advanced filters. With this technique I could solve the runtime problem with my program.
Thanks for sharing. Excellent techniques. God bless you.