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.

@Excelmacromastery

4 жыл бұрын

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.

@incobart

4 жыл бұрын

@@Excelmacromastery hi, actualy I am looking your other video that explains it .. thanks

@prayushijain4087

3 жыл бұрын

Please provide your inputs Check this too kzread.info

@TheZenytram

2 жыл бұрын

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.

@Excelmacromastery

4 жыл бұрын

That's great to hear Tom.

@prayushijain4087

3 жыл бұрын

Check this too kzread.info

This is absolutely amazing. I went from a run time of 6 minutes to 14 seconds! Thank you so much!

@Excelmacromastery

4 жыл бұрын

Thanks Peter!

Hi everyone! This video has some great practical techniques to help you speed up your code. Please add any comments below. Thank you.

@8ballWASD

4 жыл бұрын

thank you, Mr Paul! I'm going to implement it in my team. I wish you all the best in your career!

@amareshachar4335

4 жыл бұрын

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

@sonderfall1979

4 жыл бұрын

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.

@plumberski8854

4 жыл бұрын

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.

@prayushijain4087

3 жыл бұрын

Very nice video... Please provide your inputs kzread.info

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.

AWESOME!!! That's the point of macros - speed! I am happy there are ppl like you

@Excelmacromastery

4 жыл бұрын

Glad to help

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!!

@Excelmacromastery

4 жыл бұрын

Thanks Brad. Glad you have got so much use from this video.

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).

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!

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!

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!!

@Excelmacromastery

4 жыл бұрын

I wish I had watched myself 10 years ago😛

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.

Best VBA tutorials I’ve seen so far!

@Excelmacromastery

4 жыл бұрын

Thanks for your comment Mei

I feel happy that I use these techniques all the time when vba calculation time matters on some projects.

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!!

Outstanding! I have been doing it the slow way for years.

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!

@Excelmacromastery

4 жыл бұрын

Thanks Matthew

Man, this is the BEST VIDEO ON VBA in youtube! Thanks a lot.

A major work ! Very helpful and easy to integrate in all VBA code. Thank you so much

@Excelmacromastery

4 жыл бұрын

No problem Jerome.

That is some very valuable techniques! Will definitely be using them to write/REwrite macros. Thanks! 😊

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!!!

@Excelmacromastery

4 жыл бұрын

Thanks Mark. That is high praise indeed. I'm glad you got so much from the video.

This is one of the most genuinely helpful contents I have ever come across.

This is exactly what I needed. Spot on. Thank you so much!

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.

@Excelmacromastery

4 жыл бұрын

Appreciate the feedback Alphonso.

Thanks Paul. This is the best video I've ever see in learning VBA for mastery. Thanks, i will watch all your 13 videos.

@Excelmacromastery

4 жыл бұрын

Thanks for your support Blake.

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.

@aguswidi9316

4 жыл бұрын

Agree with brendan

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 :)

@Excelmacromastery

4 жыл бұрын

Thanks Zach. Glad to help.

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.

Here's a fan from Taiwan!! Thanks for this useful video, I learned a lot.

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...

I am speechless. This is wonderful stuff. Thank you for sharing. God bless!

OMG this is an awesome video. All the stuff I do at my work will be so much faster.

@Excelmacromastery

4 жыл бұрын

Thanks John.

Brilliant! I just learned lots and just wish I had known this a long time ago!

Thanks for this tutorial. Is a MUST for all 'amateur' programmers like me. Excellent and Thanks once again Regards. Martin ( South Africa)

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

This is an awesome improvements. I did not know there were so many ways to speed up the code! Thanks for sharing these tips

Wow... Nice... Advanced filters blew my mind! Thank you!

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.

@Excelmacromastery

4 жыл бұрын

You're Wecome Jawali.

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!

@Excelmacromastery

4 жыл бұрын

Thanks for the feedback Jenny. I'm glad you got so much value from the video.

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.

@Excelmacromastery

4 жыл бұрын

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.

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.

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.

@Excelmacromastery

4 жыл бұрын

Thanks Winter. Glad you enjoyed it.

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.

Thank you so much for your great videos! You are great at explaining. Im learning a lot

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

@Excelmacromastery

4 жыл бұрын

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

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.

@Excelmacromastery

4 жыл бұрын

Glad to help Deepak

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 KZread 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.

@Excelmacromastery

4 жыл бұрын

You're welcome. Glad you like them.

wow !! The Awesome techniques I got today from your channel, Thank you very much for this great knowledge !!

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

@Excelmacromastery

4 жыл бұрын

Glad you like it Sacha.

A big thanks from Germany for this very useful und powerful way to code, people like you make this world better!

@Excelmacromastery

4 жыл бұрын

Thanks Philipp

Thanks for this - I look forward to applying advance filter on current project.

You are the best VBA teacher of me, many thanks

Bravo!! great tutorial. It help me a lot to improve my data processing from multiple worksheets. Thanks a lot!

Hi Paul.. this video was super interesting and useful.. really like how you presented it logically in sequence with Advanced Filter being the speed king. Thanks for the insights and valuable tips and tricks to speed code execution. Looking forward to more. Thumbs up!

@Excelmacromastery

4 жыл бұрын

"Speed King" - that's a great title.

Thank you for sharing this, Paul. These tips are extremely helpful.

@Excelmacromastery

4 жыл бұрын

You're welcome Eric.

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.

@Excelmacromastery

4 жыл бұрын

Glad you like it.

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.

@Excelmacromastery

4 жыл бұрын

Thanks Celia. Glad you like it.

This is the double slit experiment for Excel. Thank you for breaking this down.

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!

@Excelmacromastery

4 жыл бұрын

Thanks Nick. The macro recorder code is very different than real-world code. It leads to a lot of confusion.

Thanks Paul, I like your clear step by step style. This bite sized video will have a significant impact on my approach to some tasks and see I will have to rewrite a batch of my existing routines! Using the advanced filter looks like a much more efficient,simpler and cleaner approach than pivot tables/ordinary filters to do the heavy lifting.

@Excelmacromastery

4 жыл бұрын

Great you found it so useful Chris. Advanced filter are great for copying and filtering data in record format. They are not only fast but require less code and it's simple to select or reorder columns. The one advantage that pivot tables have is that they group data whereas the advanced filter simply filters it.

Paul, this is great! Thanks. I learned something new and useful today.

@Excelmacromastery

4 жыл бұрын

Glad to hear it. You're welcome.

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

@Excelmacromastery

4 жыл бұрын

That's great to hear:-)

Great video, one of the better videos I ever watched on VBA and I have been coding / learning for few years

@Excelmacromastery

4 жыл бұрын

Thanks very much.

Thank you, I tried the first two steps, and increase 10 fold of the speed of my worksheet. Thank you for sharing!

@Excelmacromastery

4 жыл бұрын

That's great to hear Kai. Thanks for the feedback.

Your live explanation is precious for me.

Clearly, the guidelines have improved the speed of my code... excellent & simple techniques... thanks

@Excelmacromastery

4 жыл бұрын

You're welcome Shohan

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.

Eye opening stuff! Thanks Paul.

@Excelmacromastery

4 жыл бұрын

You're welcome.

I love this advanced filter trick! Thanks for sharing!

@Excelmacromastery

3 жыл бұрын

Glad you liked it!

I write lots of VBA code and I learned new tricks. I never used the advanced filter before. Thanks for creating this overview.

@Excelmacromastery

4 жыл бұрын

Glad it was helpful!

Great VBA helpful video tips from you sir Paul.

Thanks Paul, Timely informative, and pertinent, as usual.

@Excelmacromastery

4 жыл бұрын

Thanks lyonhard1.

Great stuff Paul, thanks a lot for this video.

Very valuable video...I too recommend..to my friends... Every one face these issues of slow running of macro.. Thanks for your time..

@Excelmacromastery

4 жыл бұрын

Thanks for sharing Gopi.

Paul, thanks a lot for share this knowledge with us. This is really useful for the daily job. Go ahead with your valuable work spreading all your knowledge. 👏👍

@Excelmacromastery

4 жыл бұрын

Thanks Luis.

I was aware of some of the tricks, but not really aware of the really big ones. Thank you! Well done.

@Excelmacromastery

4 жыл бұрын

You're welcome.

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.

@Excelmacromastery

4 жыл бұрын

Thanks for the feedback Tony.

This guy definitely knows his stuff! I am impressed.

application.screenupdating = false is a must for every VBA user.

@Excelmacromastery

4 жыл бұрын

That's true.

@andrewlockwood6102

4 жыл бұрын

@@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. :-)

@thatguythatdoesstuff7448

4 жыл бұрын

@@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.

@megapro125

4 жыл бұрын

@@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

@phpvbacoder9000

3 жыл бұрын

Best practice is always to implement error handling and Set back to defaults there

nothing new for me, but i like how consistently you explain) good job, definitely.

@Excelmacromastery

4 жыл бұрын

Thanks Dimon.

This is incredible awesome. Another trick I got, was about use the part of enables and disables in a unique sub and just call then. Thanks for all

@Excelmacromastery

4 жыл бұрын

Glad to help Guilherme.

You are just Great Sir with immense knowledge

Perfect. Excellent. Thanks a lot to take the time to teach us how to become our codes faster. Greetings from Venezuela. New Follower.

Awesome video and explanation. Thanks a lot for sharing this goldmine.

@Excelmacromastery

4 жыл бұрын

You're welcome Jan

Excellent! Thx. Extremely important concept in VBA

Fantastic...really useful video. Wish to have more like this.

@Excelmacromastery

4 жыл бұрын

More on the way Ashokan.

Great work and Superb method to explain in fluent and descent manner,Love you .Salute for attitude and making it available for all.Thanks

@Excelmacromastery

4 жыл бұрын

thanks very much Pradeep

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

@Excelmacromastery

4 жыл бұрын

Nice work!

Loved this Advanced filter

I knew some of them, but not advancedfilter 😲, simply amazing, thx for sharing this!

@Excelmacromastery

4 жыл бұрын

Glad to share Ronald.

Great video!! I just watched this one video of yours and I subscribed. Good job!!

Great tips, thank you!

Thank's a million, now I'will review all vba macro code cause now i'm using excel 2016, which is slowing down my macros.

That was impressive. Just turning off the calculations and screen updates saves a considerable amount of time.

@Excelmacromastery

4 жыл бұрын

Thanks Tony

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.

@Excelmacromastery

4 жыл бұрын

Thanks Arsenio

EXCELLENT, WONDERFUL, THANK YOU SO MUCH FOR YOUR CONTRIBUTION OF KNOWLEDGE