This is exactly what I am looking for. I work in telecom industry. It took me hours to process raw data.. now .. down to less than 20 mins with a humble system. Thank you .. where have you been 10 years ago ? I can't thank you enough .
Absolutely fabulous instruction. Seeing the array filled with one line of code and then watching it crunch 2500 records in a blink, was the most fun I've had in a while... sad, but still fun! Thank you Paul.
Has to be the best vba tutorial I've seen on UA-cam. Lots of garbage stuff out there. I'm a self taught VBA user, picked up a few very helpful codes and ideas watching this. Thank you
I like the format of your videos and the way you explain things. Even if I know some of them, I still watch just because you're one of the very few on youtube that is actually enjoyable to listen to.
I am self-taught and I didn't fully grasp this content the first couple of times I looked at it. I decided I needed to better understand it and it now makes sense.
Magnificent.... I have very complex range operations which take my program 281 seconds to perform. With this method I reduced the time to under 5%.... Best ever YT content I've encountered. Keep doing, don't stop. Ever! It has the potential to be great...
From someone putzing around with VBA for a few years, finding R Studio was a godsend. Perhaps there are special instances where you might have to use Excel, but I'd advise anyone who thinks they should spend time learning to code VBA to give R a try.
Amazing!! I watched tons of vba videos (I'm newbie) and by far this video gives a incredible edge because of its clear and concise information. Excellent!! Great work, please keep it up!!
Thaks!!! Have just changed the code in one of my macros to use arrays. I go through 200k lines in a table. Before, it took 3m20s, now it finishes in 12s, 16x faster!
Good stuff.... taking baby steps with my VBA project but every step is in the right direction with these videos!!... (oh and of course I have liked this video!)..
I stumbled upon something quite interesting when playing around with this. If you try to set an array, it will return the properties of each cell in a range instead of the value of the cells in the range. Very useful to get a number of properties information
mahdi zahzah je vous applaudis d’avoir pris le temps pour comprendre ce qui n’est évidement pas facile à apprendre surtout en une autre langue. Continuez de faire de votre mieux et je vous assure que vous réussirez!
Excellent video.I can see the value of arrays, as opposed to ranges, and am in the process of changing one of my user forms.However, what the video does not cover and I am interested in is the following:Placing the data from the user form back into the table/spreadsheet.
Amazing tutoring as always! Keep these videos coming!! They're making a world of a difference to my (currently limited) VBA skills. I had a thought, would you consider doing a video on how to use VBA generically? Like copy+paste files across different folders, or using VBA to send a standard email message? Not pushing for this but thought it could be interesting. Thanks!!
this is on fire guys...the arrays functions can makes us our projects much easier and fastest....i am also trying to learning this arrays functions codings,....can someone pls explain me why the sir has used i,1 and i,5 in 6:43 minutes i want to use count of columns so what would be just basic formula for counting columns in arrays.
This is really really great and has reduce the processing speed of my code dramatically. The only issue that I have encountered is writing the data back to the same location (I am pulling the data from an Excel table) that is filtered. I see very strange results where the first row of the array is inserted into the last line that is displayed. Any suggestions on how to paste back to a range / table that is filtered? I could remove the filter and then re-apply it, but if that data that is changed in the array, then the same filter would not be applied.
A million thank you, could you please suggest if we substarct same value what you have mention but I want to add column in the end and calculation value mention . Can we do that?
Thank you Paul! I was wondering if it is possible to get other cell attributes (like cell background color) and not only cell value into an array ( without iterating through the entire range)? I have a very large range which I need to get the background colors of each cell, and it takea very long to run...
Hey Paul, amazing Tutorials of yours!! Will this code also adapt to added rows within oder at the end of the original table, as it does with added rows?🙏😎🇩🇪
Your explanation is superb. No one can explain these topics so easily. Sir, would you kindly please make a video on this topic - "How to split Strings that contain no Delimiter using User Defined Function in Excel VBA?" For Example, - You have some client names on your Sheet1 from Range("A2") to Range("A16"): Raw Data: ======== Names First Names | Middle Names | Last Names ------------ -------------------- ------------------------ -------------------- AlexanderWilliamJamesBrown AmandaTaylor AriaNguyen AubreyFoster AveryAnnPhillips Beerus BenjaminThomasHarris CharlotteMitchell ChristopherJackson DanielJamesMiller DavidAndrewJohnWilliams Dende DylanJamesWard ElijahWatson EllaSophiaRoberts ========== Here, some clients have First Names, Middle Names and Last Names (For Example: Alexander William James Brown etc.); some have First Names and Last Names (For Example: Amanda Taylor etc.) and some have only First Names (For Example: Beerus etc.). Now, I want to delimitate each name based on the Capital Letter. Desired Output mentioned below: Desired Output: ============= Names First Names | Middle Names | Last Names ------------ -------------------- ------------------------ -------------------- AlexanderWilliamJamesBrown Alexander | William James | Brown AmandaTaylor Amanda | | Taylor AriaNguyen Aria | | Nguyen AubreyFoster Aubrey | | Foster AveryAnnPhillips Avery | Ann | Phillips Beerus Beerus BenjaminThomasHarris Benjamin | Thomas | Harris CharlotteMitchell Charlotte | | Mitchell ChristopherJackson Christopher | | Jackson DanielJamesMiller Daniel | James | Miller DavidAndrewJohnWilliams David | Andrew John | Williams Dende Dende DylanJamesWard Dylan | James | Ward ElijahWatson Elijah | | Watson EllaSophiaRoberts Ella | Sophia | Roberts
First of all. Thank you for taking VBA to a whole new level! These videos are great. I got stuck at the code at 3:14 . VBA says gives a compile error: "Variable Not Found" for "shData". Any idea why it is working for you but not mine? VBA version is 7.1.
Thanks for the video, Paul! I had a question! I'm looking to copy all the info from a sheet and paste it to another sheet. Based off the indent value of a second column, I will offset it on a row in the new sheet(higher indent = higher offset, indentlevel 1 goes to A:D on row 1, indentlevel 2 goes E:H on row 2, ect) then continue through the entire sheet. I'm seeing if I can apply this to that but the problem is my first row has an embedded link.. is there a way to add that into the array and keep the link as well when it gets pasted?
I'm far from a newb with VBA and I'm still learning some things I didn't know from your vids, thanks! Question : I see you use .value I have always been told to use value2 because it is allegedly faster and gives the underlying value...thoughts?
I use Value for almost everything and I never have a problem. There may be specific cases where you need it: "The only difference between this property and the Value property is that the Value2 property doesn't use the Currency and Date data types." -docs.microsoft.com/en-us/office/vba/api/excel.range.value2
Value2 seems to be faster (for all types) and safer (for currencies and dates): fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/ I recreated the analysis in that blog with similar favorable metrics for Value2 over Value.
I know this video is old. But want to ask this question of you or anyone who reads it. Is there an equivalent query-generating function/feature for Excel like there is for MS Access? That one is so slick and easy to use. I want to have a table of categories with multiple columns from which criteria can be set to filter only those that meet or exclude them. I don't see a way to easily do that in Excel. If not, can you create a video on how that's done? Thanks.
Hello i have one column that contains filenames in unicode, but when i assign the range to the array, the array don't have the true filename, it seems like be in ansi encoding, what can i do in order to get the true names in the array?
Hello. Wonderful lecture! Thanks. My question: is there a way by which I store my database table in an array when my workbook opens AND use/call the array at different times wherever required??
Thanks, very informative. I have a question though: How do I manage to format my cells to achieve a good layout? My current Excel sheet is rather slow as I copy a row and insert it, but with this (slow) technique I don't have to care about number/text/date/currency formats or cell formats (yellow background, bold numbers) etc. as Excel takes care of that for me. Should I work with arrays and after my "tables" are filled with data, another macro formats the cells according to my wishes? Or is there another approach?
I "tweaked" my Excel spreadsheet and was able to speed up the operations by a factor of 17 (of course, I did not use 'select' and similar bad techniques from the start). I did this by using arrays and, instead of adding one row per operation, added all the needed rows at once and inserted the array contents into those empty with one go. The format of the cells (styles, number formats, borders...) is fixed at the end for the entire "table". Thanks :)
Hello Sir, can you help me? I really need your help in coding (including more Listbox to 1Listbox) .Listbox1 (10row and 15column) and Listbox2 (10row and 15column) and Listbox3 (10row and 15column) addTo Listbox4 (30row and 15column). Thank you for all the guidance
Thanks for your Awesome Video Sir. We are selecting the around 5 columns in array and copy, pasting it to different columns arr with same 5 columns. But I have different scenario. So my question is can I select 5 columns in array, copy, paste only any 3 column Eg. Column A, B, E.. Only by using array. Please help me Or Give me alternate idea
Can you have a UDF in an Add-in and extract information from a named range within the same Add-in from one of the internal worksheets? Or is the vba module the only part of the Add-in that can be used/accessed?
Great video as usual. Quick one. I've been coding VBA for approx 5 years and consider myself a moderate to advanced user. One thing I encounter when setting ranges and pasting arrays back it pastes values and I loose formulas. Is there a method of inputting formulas into the array when it is pasting back out?
thanks paul - one question - how would you use array if you want to grab ranges that are not congruent (i.e. range(a:a), range(c:c), etc.) in one go? it seems that array = range doesn't like this. i've tried to use union to do that but no luck.
Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/
This is exactly what I am looking for. I work in telecom industry. It took me hours to process raw data.. now .. down to less than 20 mins with a humble system. Thank you .. where have you been 10 years ago ? I can't thank you enough .
Best vba channel i ever found !
X2
Dear Kelly Thank you and I want to learn more about Excel VBA
The information you give us is so clean, easy-to-understand and implement… I just love your channel, Im hoping to buy the handbook soon
Thanks Gonza
Absolutely fabulous instruction. Seeing the array filled with one line of code and then watching it crunch 2500 records in a blink, was the most fun I've had in a while... sad, but still fun! Thank you Paul.
Glad you liked it Michael.
Best Video on the Interweb , Internet & Galaxy !! Star Paul !! Your an Amazing Star !!
20 Minutes to 33 seconds!!! Thank you very much.
Has to be the best vba tutorial I've seen on UA-cam. Lots of garbage stuff out there. I'm a self taught VBA user, picked up a few very helpful codes and ideas watching this. Thank you
Glad you liked it.
I like the format of your videos and the way you explain things. Even if I know some of them, I still watch just because you're one of the very few on youtube that is actually enjoyable to listen to.
Thanks
I am self-taught and I didn't fully grasp this content the first couple of times I looked at it. I decided I needed to better understand it and it now makes sense.
Magnificent.... I have very complex range operations which take my program 281 seconds to perform. With this method I reduced the time to under 5%.... Best ever YT content I've encountered. Keep doing, don't stop. Ever! It has the potential to be great...
Thank you Paul for all your content!!!
Dictionaries, arrays, collections and classes are the best.
This video got me up a high wall...by making my code cut thru a dead end(error) and in a fast way...Genius!
From someone putzing around with VBA for a few years, finding R Studio was a godsend. Perhaps there are special instances where you might have to use Excel, but I'd advise anyone who thinks they should spend time learning to code VBA to give R a try.
Where have you been all my life? This is awesome!
Amazing!! I watched tons of vba videos (I'm newbie) and by far this video gives a incredible edge because of its clear and concise information. Excellent!! Great work, please keep it up!!
Thaks!!! Have just changed the code in one of my macros to use arrays. I go through 200k lines in a table. Before, it took 3m20s, now it finishes in 12s, 16x faster!
That's great to hear.
Incredible how efficient you codes are, yet explain it so simple. Genius!
Good stuff.... taking baby steps with my VBA project but every step is in the right direction with these videos!!... (oh and of course I have liked this video!)..
I stumbled upon something quite interesting when playing around with this. If you try to set an array, it will return the properties of each cell in a range instead of the value of the cells in the range. Very useful to get a number of properties information
If you use Set with a variant to a range it will return the range rather than the array.
Great stuff Paul, big fan From Florida USA....
Absolutely a clear and concise explanation of using arrays. Well done! You gained another subscriber.
Great job Paul. Love using arrays now. So much more efficient.
Mr Kelly, you are a champion 🏆 😎👍
Thank you so much for the very useful tips using arrays in vba!! :)
Clear and concise, I love it!!!
Great VBA teacher, I've tried various different tutorials and keep coming back here!
Thanks for your support my videos Andrew
I have done some of this before, but not the resize aspect. Great tips, thanks Paul.
Why does this have only 69k views? It should be on the top of the recommendations on UA-cam!
as always all your videos are amaizing and greats!
Absolutely loved this tutorial! Thank you sir!
You're a beast. Your content is the best.
Wow...i now really appreciate arrays. Very efficient compared to ranges. Thanks Paul
BEST VBA ADVICE EVER!!!! Wow, you are awesome :-)
Glad you think so!
Mille merci Mr
Malgré que je suis très faible en anglais mais j'essaye le maximum de vous suivre car je sais bien que votre vidéo est très utile
mahdi zahzah je vous applaudis d’avoir pris le temps pour comprendre ce qui n’est évidement pas facile à apprendre surtout en une autre langue. Continuez de faire de votre mieux et je vous assure que vous réussirez!
Thanks a lot! A very clear explanation that addressed exactly what I was looking for. You're a great teacher!
Excellent video.I can see the value of arrays, as opposed to ranges, and am in the process of changing one of my user forms.However, what the video does not cover and I am interested in is the following:Placing the data from the user form back into the table/spreadsheet.
Amazing tutoring as always! Keep these videos coming!! They're making a world of a difference to my (currently limited) VBA skills.
I had a thought, would you consider doing a video on how to use VBA generically? Like copy+paste files across different folders, or using VBA to send a standard email message? Not pushing for this but thought it could be interesting. Thanks!!
Awesome paul.... U r the master of vba... Great to have u as a teacher
Glad you like it.
Thanks Man! you and Leila are the best in excel tips!
this is on fire guys...the arrays functions can makes us our projects much easier and fastest....i am also trying to learning this arrays functions codings,....can someone pls explain me why the sir has used i,1 and i,5 in 6:43 minutes
i want to use count of columns so what would be just basic formula for counting columns in arrays.
Awesome work!
Really awsem, never seen such effective and shorter code in my life, again thanks for creating this video, love you, really helped me a lot
You're welcome!
Amazing teacher, each video I watch I learn a new trick. Thanks a lot
Thanks for your comment George
Thank you for what you have taught, see if it is possible with an array to load a listbox with more faith than 10 columns faster??? greetings
Another really 1st class video, really simple and robust code that covers so many practical situations
Many thanks, excellent and well explained, it is crystal clear, your video helped me a lot.
Glad you liked it Adam.
Very nice explanation..... keep it up ....
From Algeria, thank you Paul for this vidéo.
Thankyou so much for this tutorial, really helped me a lot! and saved my time.. thanks a lot yet again!! :))
amazing video, thanks, you are my best vba mastery legend.
Wow, thanks
Thank you so much. Great video. I am learning a lot. Very useful tips.
This guy is a frickin' genius.
Mindblowing 💯💯
This is really really great and has reduce the processing speed of my code dramatically. The only issue that I have encountered is writing the data back to the same location (I am pulling the data from an Excel table) that is filtered. I see very strange results where the first row of the array is inserted into the last line that is displayed. Any suggestions on how to paste back to a range / table that is filtered? I could remove the filter and then re-apply it, but if that data that is changed in the array, then the same filter would not be applied.
Great work Sir, you real world examples are so fascinating. I have a question to in the same situation.
Thank you. Great video.
you are a G.O.A.T. probably also take us through append
A million thank you, could you please suggest if we substarct same value what you have mention but I want to add column in the end and calculation value mention . Can we do that?
This is a fascinating topic. I think I can adapt this to copying non-contiguous ranges from one workbook to another, but I'm not quite sure.
You can @ Eduardo Chavarria
Thank you Paul! I was wondering if it is possible to get other cell attributes (like cell background color) and not only cell value into an array ( without iterating through the entire range)? I have a very large range which I need to get the background colors of each cell, and it takea very long to run...
Paul this is awesome. Can this be used in Class Module. if not please show us how to do it. Thanks in advance.
06:49 - Was Wow--- In one line we can paste the range that we work on
Awesome! Can we access to the cell properties and methods within the array?
A query, in an array (not in excel cells) is it possible to include background color? or put background color to certain elements of that array?
Thanks for uploading this Paul.
Hey Paul, amazing Tutorials of yours!! Will this code also adapt to added rows within oder at the end of the original table, as it does with added rows?🙏😎🇩🇪
Your explanation is superb. No one can explain these topics so easily. Sir, would you kindly please make a video on this topic - "How to split Strings that contain no Delimiter using User Defined Function in Excel VBA?" For Example, - You have some client names on your Sheet1 from Range("A2") to Range("A16"):
Raw Data:
========
Names First Names | Middle Names | Last Names
------------ -------------------- ------------------------ --------------------
AlexanderWilliamJamesBrown
AmandaTaylor
AriaNguyen
AubreyFoster
AveryAnnPhillips
Beerus
BenjaminThomasHarris
CharlotteMitchell
ChristopherJackson
DanielJamesMiller
DavidAndrewJohnWilliams
Dende
DylanJamesWard
ElijahWatson
EllaSophiaRoberts
==========
Here, some clients have First Names, Middle Names and Last Names (For Example: Alexander William James Brown etc.); some have First Names and Last Names (For Example: Amanda Taylor etc.) and some have only First Names (For Example: Beerus etc.). Now, I want to delimitate each name based on the Capital Letter. Desired Output mentioned below:
Desired Output:
=============
Names First Names | Middle Names | Last Names
------------ -------------------- ------------------------ --------------------
AlexanderWilliamJamesBrown Alexander | William James | Brown
AmandaTaylor Amanda | | Taylor
AriaNguyen Aria | | Nguyen
AubreyFoster Aubrey | | Foster
AveryAnnPhillips Avery | Ann | Phillips
Beerus Beerus
BenjaminThomasHarris Benjamin | Thomas | Harris
CharlotteMitchell Charlotte | | Mitchell
ChristopherJackson Christopher | | Jackson
DanielJamesMiller Daniel | James | Miller
DavidAndrewJohnWilliams David | Andrew John | Williams
Dende Dende
DylanJamesWard Dylan | James | Ward
ElijahWatson Elijah | | Watson
EllaSophiaRoberts Ella | Sophia | Roberts
First of all. Thank you for taking VBA to a whole new level! These videos are great. I got stuck at the code at 3:14 . VBA says gives a compile error: "Variable Not Found" for "shData". Any idea why it is working for you but not mine? VBA version is 7.1.
I figured it you. Looks like you are referencing the sheet name under Microsoft excel objects.
Great video !! Thanks for sharing with us !
Amazing explanation! Congrats!
Thank you for the video :) It was helpful for me.
Glad it helped!
Paul, Thank you for great instruction. Is it possible if I do samething with a recordset pulled out of query?
Thank you Paul a brilliant video again,
Always great stuff, very well presented
Thanks Gary
Really awesome! Never seen such effective and shorter code in my life, again thanks for creating this video, love you, really helped me a lot!!
Learning a lot. Thanks!!
Thanks for the video, Paul! I had a question! I'm looking to copy all the info from a sheet and paste it to another sheet. Based off the indent value of a second column, I will offset it on a row in the new sheet(higher indent = higher offset, indentlevel 1 goes to A:D on row 1, indentlevel 2 goes E:H on row 2, ect) then continue through the entire sheet. I'm seeing if I can apply this to that but the problem is my first row has an embedded link.. is there a way to add that into the array and keep the link as well when it gets pasted?
Great tips, thanks!
Fatastic video. Thanks
hi thanks for your videos... is it possible to redim preserve arr(1 to x,1) when arr is a variant ? thanks
You sir, are next level. AMAZING!!!
Thank you so much! This is great.
I'm far from a newb with VBA and I'm still learning some things I didn't know from your vids, thanks! Question : I see you use .value I have always been told to use value2 because it is allegedly faster and gives the underlying value...thoughts?
I use Value for almost everything and I never have a problem. There may be specific cases where you need it:
"The only difference between this property and the Value property is that the Value2 property doesn't use the Currency and Date data types." -docs.microsoft.com/en-us/office/vba/api/excel.range.value2
Value2 seems to be faster (for all types) and safer (for currencies and dates):
fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/
I recreated the analysis in that blog with similar favorable metrics for Value2 over Value.
@@dougdevine27 Thanks, good link...I'll stick to using value2...but I'm sure for most real world cases it makes little difference
I know this video is old. But want to ask this question of you or anyone who reads it. Is there an equivalent query-generating function/feature for Excel like there is for MS Access? That one is so slick and easy to use. I want to have a table of categories with multiple columns from which criteria can be set to filter only those that meet or exclude them. I don't see a way to easily do that in Excel. If not, can you create a video on how that's done? Thanks.
Always loved watching your videos.
Just a quick question.
How can I adjust this to lets say I want Columns A, C to E.
thank you very much!
Hello i have one column that contains filenames in unicode, but when i assign the range to the array, the array don't have the true filename, it seems like be in ansi encoding, what can i do in order to get the true names in the array?
Is there an effective way to use the same array approach to also copy and transfer the cell format to the new range?
Hello. Wonderful lecture! Thanks.
My question: is there a way by which I store my database table in an array when my workbook opens AND use/call the array at different times wherever required??
Excellent video.!!!!!!!!!!!!!
Thank you very much!
Thanks, very informative. I have a question though: How do I manage to format my cells to achieve a good layout? My current Excel sheet is rather slow as I copy a row and insert it, but with this (slow) technique I don't have to care about number/text/date/currency formats or cell formats (yellow background, bold numbers) etc. as Excel takes care of that for me. Should I work with arrays and after my "tables" are filled with data, another macro formats the cells according to my wishes? Or is there another approach?
I "tweaked" my Excel spreadsheet and was able to speed up the operations by a factor of 17 (of course, I did not use 'select' and similar bad techniques from the start). I did this by using arrays and, instead of adding one row per operation, added all the needed rows at once and inserted the array contents into those empty with one go. The format of the cells (styles, number formats, borders...) is fixed at the end for the entire "table". Thanks :)
Hello Sir, can you help me? I really need your help in coding (including more Listbox to 1Listbox) .Listbox1 (10row and 15column) and Listbox2 (10row and 15column) and Listbox3 (10row and 15column) addTo Listbox4 (30row and 15column). Thank you for all the guidance
Thanks for your Awesome Video Sir. We are selecting the around 5 columns in array and copy, pasting it to different columns arr with same 5 columns. But I have different scenario. So my question is can I select 5 columns in array, copy, paste only any 3 column Eg. Column A, B, E.. Only by using array. Please help me Or Give me alternate idea
Thanks a lot, this is awesome.
Can you have a UDF in an Add-in and extract information from a named range within the same Add-in from one of the internal worksheets? Or is the vba module the only part of the Add-in that can be used/accessed?
Great video as usual. Quick one. I've been coding VBA for approx 5 years and consider myself a moderate to advanced user. One thing I encounter when setting ranges and pasting arrays back it pastes values and I loose formulas. Is there a method of inputting formulas into the array when it is pasting back out?
thanks paul - one question - how would you use array if you want to grab ranges that are not congruent (i.e. range(a:a), range(c:c), etc.) in one go? it seems that array = range doesn't like this. i've tried to use union to do that but no luck.
Hello, Thanks for this tutorial. However, my problem here is that it can't include data after blank row or blank column. Any tips? Thanks
Awesome content. Keep sharing useful tips and tricks.
It is much helpful for developers life. :) :)