VBA Array with Filter, Sort and Slice (2021)

[@gertverboomen]

KZbin should add an additional button because a thumbs up is a large understatement for this kind of quality content! Thank you sir, I have learned a lot from your videos, highly appreciated!

[@Excelmacromastery]

Wow, thanks Gert!

[@TimePasser]

Agreed .. he is precise and knows how to deliver in an easy language . Eagerly awaiting his next video. It's like he knows exactly my problem and he guides me through his vids.. keep it up.

[@ExcelPowerPythonHub]

Same with me 😊

[@Orumaiti]

There are probably millions of VBA coders struggling with these array manipulations, and you got just what the doctor ordered. I wish Microsoft would add your classes by default so that all could benefit from your excellent work!

[@Excelmacromastery]

Thanks Ori.

[@sum1razz]

I've added dozens of functions over this class for my personal use, such as sumif, getunique....Life has never been easier!!!

[@SimpleExcelVBA]

Wow, I'm impressed by the amount of work You've put in this class and now You're sharing it with others, awesome!

[@marcesil78]

This wanderfull, Paul! Your videos always teach me a lot. Thank you very much! Greetings from Paraguay.

[@Excelmacromastery]

Thanks Marcelo

[@gregbernard7861]

This is now my go to VBA channel.

[@sandeepagarwal7387]

This is what is - Sharing Wealth...and what can be a greater wealth than knowledge....may be with only exception of 'Health' wealth :) Hats off to you, Paul!

[@giovanniscafaro4354]

Ho avuto la possibilità di studiare manuali e frequentare diversi corsi promossi da programmatori su VBA e posso affermare che Paul Kelly è un eccellete docente!. Grazie a Te Paul Kelly ho compreso concetti profondissimi sul linguaggio di programmazione VBA come le Classi, Interfacce, Collection, Dictionary, Array ecc.., Che Dio ti benedica e sia propenso a farti raggiungere le "alte vette" professionali che abitano nel tuo cuore di insegnante.

[@Excelmacromastery]

Translated "I have had the opportunity to study manuals and attend several courses promoted by programmers on VBA and I can say that Paul Kelly is an excellent teacher !. Thanks to you Paul Kelly I understood very deep concepts on the VBA programming language such as Classes, Interfaces, Collection, Dictionary, Array etc .., May God bless you and be inclined to make you reach the professional "high peaks" that live in your heart as a teacher." Thanks Giovanni, I appreciate it.

[@sum1razz]

Unmatched. This saved a tons of time and effort for my VBA application. It would be nice we can get a unique array of items given the column index.

[@Max-vn9mu]

De acuerdo con estos comentarios. Lo mejor que he visto en KZbin. Calidad y cantidad. Y eso que no es mi idioma. Pero vale la pena el esfuerzo porque es lo mejor que hay por lejos de VBA en KZbin. Thank you a lot for your contents. Really appreciated

[@joaocustodio2094]

Paul, absolutely fantastic. You never ceases to amaze me. From Portugal, a huge thanks.

[@fernandomartinorzaez7897]

Hi, you are the Best vba programmer and efective teacher that I follow. Thank you very much. Please make more videos.

[@Excelmacromastery]

Thank you Fernando.

[@yaoyao854231]

What an amazing job you have done and provided , highly appreciated !

[@Excelmacromastery]

Thank you kindly!

[@alexandrumoldovan9936]

The content that this channel provides is just amazing. Following previous videos I switched the majority of data manipulations to arrays because they were much faster. They worked wonders but changing them was hard work, even just adding/removing a row or filtering data required a lot of code, BUT NOT ANYMORE :D!! Keep up the great work. Huge thanks!

[@Excelmacromastery]

Great to hear Alexandru!

[@LuffyFA]

This is incredible, Paul! Your videos always teach me a lot. Thank you very much! Greetings from Brazil.

[@Excelmacromastery]

Glad you like it😀

[@balakumar.n4891]

I appreciate this work! Keep making more contents. I look forward to attend the webinar today.

[@karenjoy511]

Super cool functions. I wrote an array manager class without knowing a bunch of stuff and although it works well, it's certainly not as slick as yours!

[@petercoombes6602]

Fabulous Class Module! Thank you for making my work even easier! Now I see a simple solution to combine this with your Dropdown Search class method to do searches on multiple columns from multiple textboxes.

[@Excelmacromastery]

You're very welcome Peter!

[@subhramanyemdudey2941]

Amazing and an excellent one Paul.Every time I see your video I learn something new

[@Excelmacromastery]

Glad to hear that!

[@tienta4424]

Verry impressive for the performance. i hope we will have a sort by column in the near future.

[@EricHartwigExcelConsulting]

Fantastic video! Eagerly awaiting the workbook source code download so I can go through it in more detail. Thank you for creating and sharing this video!

[@sawanb2337]

Thank you I learn something new every time I watched your videos.

[@Excelmacromastery]

Glad to hear that!

[@wayneedmondson1065]

Thanks Paul. That's some powerful magic you've created. Thanks for sharing it :)) Thumbs up!!

[@Excelmacromastery]

You are very welcome Wayne.

[@Planet_Xplorer]

Brilliant! You're creating your own version of Python Pandas! :-)

[@cloudli4217]

What amazing training!!! Thank you so much!

[@udayp1639]

Can you show how to save the functions and class in personalmacro workbook and how to access the functions in other workbooks?

[@AnilKumar-vi8oe]

Yet again very usefull information, thank you so much for your effort

[@Excelmacromastery]

You're welcome Anil.

[@Excelmacromastery]

Let me know if you like this Array class in the comments. Don't forget to download the source code with tons of examples from the video description. Table of Contents: 00:00​ - Introduction 00:11​ - Why use Arrays? 02:30​ - The problem with the standard VBA array 02:43​ - Adding the array class to your code 06:26​ - Inserting Rows 07:51​ - Removing rows 08:32​ - Searching the Array using IndexOf 10:35​ - Basic Filtering 11:35​ - Filter using custom functions 13:49​ - Create a compare function easily 14:59​ - IndexOf with a custom function

[@bogdanexit1]

I liked !!! only if as recive the email with the code , i tried in the past to download other code but mismatch , maybe my email has a problem

[@Husky_Passion]

a link who needs my email is already a promise of spam to me

[@saidtaliby]

@@bogdanexit1 The do you have the infinite wait cursor? I disabled the adblocker and the download worked (mine was Ublock Origin).

[@BenjaminHouot]

Excellent Paul ! and it is very nice to you to share the code. Maybe you could implement Sort Method that allows a Compare fonction :o. It will be perfect. Of course the compare methode should return -1, 0 or 1.

[@Excelmacromastery]

That's a good idea Benjamin. I may update the sort in the future.

[@freeliberalminds]

Thanks for this video tutorial sir EMM. This is very helpful to my project.

[@MisplacedTrust]

Another quality production. Thankyou for your work

[@Excelmacromastery]

Many thanks Anthony!

[@johnhoel3768]

I used your example to create a filter class, worked perfectly, except for one problem. In clsCompareApple, you show the first argument to iCompareFunction_Compare as ByVal, which causes a compile error. It should be ByRef. This feature made my code a lot simpler. Thanks!

[@Excelmacromastery]

You're welcome

[@Trucpq]

Thanks a lot for clsArray. It is amazing. But I try arr.Sort , it seems not to be proper

[@davescott7680]

Awesome, I have a bunch of functions i use for similar functionality that add to as need it. But I like the completeness and polish of this, think I'll use this class instead.

[@Excelmacromastery]

Thanks Dave.

[@izradawebshopa]

Hi, great lesson, thank you.

[@frikduplessis8849]

Thank you Paul great video

[@Excelmacromastery]

Glad you enjoyed it Frik

[@christopherthomas5659]

This is fabulous thank you so much. What is the simplest way to format the columns using this method? I have been trying to format the source and destination and they are not affecting the end results. I have strings that resemble dates and keep formating that way when the array is output to the destination.

[@andreykotkovets6868]

Great job, Paul! Many thanks Is it possible to initiliaze array once (as the data range will not be changed), on workbook open for example?

[@keithturner2889]

Very useful code, but how can I detect when either the filter or filterbyfunction procedures return no data?

[@stavrous100]

Excelent work! is there a way to use sort with multiple columns?

[@brunoamaral3091]

This is incredible, Paul! Thanks for sharing the code! One doubt, How can I load the array after the filter in a listbox control?

[@ktylman]

Received an 'Error (404)' from the download link - could you update this excellent resource? Many thanks in advance.

[@MiguelSeijas-f6z]

Thank you Paul for this usefull teachings, but when I try to drop the clasess appears this error: The file 'clsArray2D.cls', is marked as a version not compatible with the current version of Visual Basic and cannot be loaded. Can this be fix? Thanks a lot.

[@tomcarr9692]

Can this be used to filter on dates in the array? > as well as =? If so, would this need to be hard coded like your example or could it be a variable?

[@whiteflag2366]

It is a bit of overkill, but It is pretty awesome. Thanks for sharing!

[@gutolima7617]

There is an amazing video! Thank you Paul. By the way, I got a question is these builts-arrays filters faster than the other video you did about using advanced filtering function of vba?

[@Excelmacromastery]

Depends on what you're doing. They have different applications. Advanced Filter is extremely fast to run but if your code runs it multiple times it tends to slow down a lot. The Array isn't quite as fast for the same filter but it is more than fast enough in most situations and doesn't slow if you run operations multiple times.

[@sum1razz]

hey Paul. I have been trying to get around with .rowCount and .rowEnd method for the class. What is the real difference? As far as I can see, they both give the same result

[@naliu3399]

Thanks for the fantastic video! This is exactly what I want 👍👍 I tried to download but the link to source code doesn’t work any more. Is there another alternative? Thank you

[@naliu3399]

I removed pop up blockers and still 404 error …

[@tc2290]

Any chance the source code is still available for this video? The link seems to have expired.

[@haicalis]

I don't seem to be able to download the code (Edge and Firefox). Would appreciate it if someone could help. Great videos by the way.

[@joelwong3189]

Hi, Thank you for the great content as usual! Would like to ask if it is possible to pass the array to another workbook? I got the error: "Object doesn't support this property or method" when I tried to write data to a range from another workbook. Thanks!

[@Excelmacromastery]

Hi Joel, Yes you can write to any worksheet in any workbook. What line do you get the error on?

[@joelwong3189]

@@Excelmacromastery Thanks for your quick response! I managed to find out why; I had some wrong referencing of workbooks. One other thing to highlight is that for Error trapping, it should not be set to "Break on All Errors". Otherwise, there will be issues with the "Do.. While" loop inside the "CheckDimensions" functions in the class clsArray2D. Just to highlight!

[@Excelmacromastery]

One unfortunate thing in VBA is the best way to check if something exists is often by getting and suppressing an error. Then as you say if you are debugging using "break on all error" it will stop on the line. It's not the end of the world but definitely inconvenient.

[@olive1782000]

Hello Paul, is it possible to add a function in the DatafromRange : possibility to removeHeaderRight and headerRightCount ? It could be useful ? Thanks

[@Excelmacromastery]

Yes, absolutely. You can easily alter the code to do this.

[@mehmetturan8500]

thank you so much for traning videoes. If you can make filter with array more then one column video, it will be better

[@johnsalkeld1088]

How do you expect the lambda functions in current excel to impact this? Especially filter by

[@Kate-gk4dy]

This is amazing, but I have a situation, it would be awesome if you can give me some advice. I have to write data from an array to a Range, but sometimes the user has filters activated over the sheet, so when I run the writedataToRange the info its printed in a wrong way. Thank you so much for your work.

[@nealonions7654]

Very good stuff here. I tend to use list objects rather than arrays or ranges. I notice you state the performance of arrays is faster than ranges. Does the same apply with list objects on large datasets?

[@Excelmacromastery]

An array is always faster than operations that requires called to the worksheet.

[@budiyanto1531]

Hii Paul. Can you, please, make video about automation of filling a form in a website using vba excel?

[@JBFschr]

Paul, I'm having trouble downloading the code for this and all your other videos. (All I see is a pulsating circle ... no download activity). Is there a problem with web page or do I need to become a member somewhere. Thanks in advance for the help and for your fantastic videos.

[@Excelmacromastery]

You need to turn off any pop up blockers like uBlockOrigin

[@Jee1978]

thanks a lot. Great video. I do have my own array class but it is nowhere as refine as yours. I will try it for sure. But my company had the great idea to switch to google suit instead of ms office so I might not be using it for very long. I Can’t believe it...

[@Excelmacromastery]

You're welcome. Sounds rough. Maybe time to move:-)

[@Rizwan1875]

Can you please make a video on how we can add/subtract larger numbers of 20 character lenght using vba?

[@juanmanzoni1354]

Hi, trying to copy the cls into my workbook error non compatible with visual basic current version

[@miguelseijas2719]

Hello, thanks a lot for these excellent information. I got an error trying to include the clsArray2D.cls to Visual Basic. It throws this error: the file is marked with a version that is not compatible with the current version of visual basic. It's something like this, becase I am a spanish speaker and the error is shown in spanish. Thank you very much for your helping me to solve this because I need this class a lot.

[@rukbeatjung]

😲Amazing!!! Thank you👍🏻

[@Excelmacromastery]

Glad you liked it!

[@norulzanamohamedsamuri1364]

could you please share how you create this class module from scratch. tq in advance

[@Ajh9998]

Hi Paul, very interesting video. Unfortunately I cannot download the workbook. Is the link broken? When I click on "Get the Code" it never finishes loading.

[@Ajh9998]

Solved ... when I switched to Chrome the download worked. Thanks :)

[@Excelmacromastery]

Glad you got it sorted out

[@dainluso]

That's really great stuff your doing here. I very much appreciate your lessons and learn a lot from it. Especially this video push me ahead. Just one thing remained open as you didn't talk about sorting an array. Would you give another short explanation on that? Or just add a simple command line as a reply to this comment how to sort an array, like: arr2 = arr1.sort(2)? I can't figure out myself. Would be great.

[@transon6586]

Hi Paul. Received an 'Error (404)' from the download link - could you update this excellent resource?

[@FernandoZacco]

Hi Paul, I've tried to add "clsArray2D.cls" to my own project but it's being rejected. It seems it's not compatible with my VBA version. Could you help me on this please?

[@xshutupx2011]

Thanks for the work. It is awesome. But the link to the source code not working. Kindly update it please.

[@CollDott]

Array default is case insensitive?

[@olliehopnoodle4628]

Is this a standard MS class or an add on you are providing? Very cool indeed.

[@Excelmacromastery]

It's a class module that I created.

[@olliehopnoodle4628]

@@Excelmacromastery That's awesome that you created it and are sharing it. Many likes.

[@clerisduchrist1253]

Thank you very much !!

[@pedraodepirapora]

In Column 4 I have the following: 01/05 When using Sub TestFilterBasic() the information is returned as 05/jan, which is exactly Day and Month (dd/mm) in Portuguese. How do I return exactly 01/05?

[@josejoaquinramirezdeltoro3774]

Muy útil. Gracias

[@songokussj4cz]

Very nice functionality! Will try that out for sure. Is this Array class capable of getting the value of a Column/Row intersecting cell? I use this a lot in my project with ListObject tables. For example: arr.getValue(row:="7", col:="Sales") would get me the value=10. If the array would start on Fruit column, arr.getValue(row:="Pear", col:="Sales") would get me 98.

[@Excelmacromastery]

Thanks Son. You can get a cell using the *Value* property but only using the number indexes e.g. _arr.value row:=5, column:=3_ Using the text is a neat idea.

[@mikeroach3301]

Hi Paul, thank you for the channel, you've got some great content! I'm running 365 in a Windows 11 VM on top of Windows 10. When I drag and drop the array class it gives me an error dialog stating, "The file is marked as a version not supported by the current version of Visual Basic, and won't be loaded." I opened the .cls with Notepad, I assume the error comes from the first directive VERSION 2.0 CLASS. I've been writing VBA for quite some time, but you've got me here, I don't understand your code before Option Explicit. I Googled for an explanation but didn't find anything. Do you have a resource you can point me to so I can enlighten myself? Also, I copied and pasted your code into a class module and commented out everything before Option Explicit, no errors remain, do you feel the code will still run properly? I've noticed other oddities running Win11 VM on top of Win10, I have a hunch it may just that. Your comments are appreciated!

[@junjing2769]

Hey Paul, I find an issue in the code, which is inconsistent with your code. That the iCompareFunction's first argument is ByVal in your video, but ByRef in the code you share with us. And it leads to some issues when doing a multi-condition filter. I am totally new to the VBA field, I am not sure whether this is a problem, anyway just want you to know that, And thanks for sharing this wonderful video with us.

[@Excelmacromastery]

Hi Jun. It should be ByRef or the filter will run very slow.

[@junjing2769]

@@Excelmacromastery Oh, that is the reason, now I got it, thank you, Paul.

[@RajaRam-co9zz]

getting an error "the file is marked as a version not supported by current version of visual basic" , help please

[@francois7491]

You're a real wizzard! How do I get hold of the clsArray2D class? The link to download the content does not seem to work. Put my email in and selected "Get Code", but nothing happens.

[@Excelmacromastery]

Hi Francois, Make sure to turn off any pop up blockers in your browser.

[@dermotbalson]

Thanks for all the work involved. I work with VBA arrays all the time, so I was interested to see what functions you created, and that they are somewhat different to those I use. My work, like that of many analysts, doesn't involve changing the data so much as summarising, filtering and making sense of it. So I often need to get a unique set of IDs from a data set, and then collate totals for each ID. I use a dictionary for this, which is extremely fast. When I sort, I create a sorted linked list using quick sort, so I am only sorting and rearranging one column, which is much faster than sorting all the data columns continuously, when you have thousands of rows of data. After the sort, I can quickly create a new array from the old, using the sort order in the linked list. A small point - I notice you add ".value" when accessing array values. Perhaps you do this for clarity, but it isn't needed, being implied if omitted.

[@Excelmacromastery]

Hi Dermot, That's a really nice way to do the sorting. I used an existing quicksort algorithm which has been around a while. I might try your method and compare the speeds it would interesting to see the speed difference. "I notice you add ".value" when accessing array values" - did you mean Range rather than array? The value is the default in most cases. In some cases, like adding the range to a collection, it will add the actual range rather than the value.

[@naveenkumardks678]

Thank you so much

[@Excelmacromastery]

You're most welcome

[@seamushand8439]

Thank you so much - now i have no excuse to not use Arrays in future. Thanks Paul

[@Excelmacromastery]

Happy to help!

[@straight6604]

The link to source code doesn't work. It's not clear whether the array class is custom or part of VBA.

[@phonesolo8513]

Thank you....

[@kamillaogjeppe1561]

Hi Paul I can't download source code - can't connect to dropbox - please help.. Jeppe

[@stevenriley6255]

Great video, but the link to the source code doesn't, just forever looks like a page trying to load something.

[@Excelmacromastery]

Turn off any popup blockers. That's the most likely cause.

[@Антоха-ц4ю]

Cant download the source files :(

[@axelamoe]

No clsArray2D option comes up for me. Any ideas?

[@axelamoe]

Also do you recommend Array instead of range for large data sheets? Almost 7000 rows. I tried array and its running slower with a bunch of if statements

[@Excelmacromastery]

You need to download the clsArrsy2D code from the description. Arrays are faster than Ranges. You may be doing something wrong to make them slower.

[@miteshpatel7101]

Sir what font do you use in vba

[@Excelmacromastery]

Consolas

[@vijaysahal4556]

sir m not done with arr .daraToRange bcz parameter is not visiable here

[@Excelmacromastery]

I don't understand your comment.

[@rolandtobiasz]

The links don't seem to work.

[@Excelmacromastery]

Make sure you don't have a pop up blocker turned on or try a different browser. It's been downloaded by 30 people at this time so should work for you.

[@MaydayAggro]

I am a near- to complete-novice at vba. I see your "cls..." autopopulates. Is this new vba code? Do i have an older version, or do the classes get created as you name them? Like i said, novice. Edit: ugh. From my small amount of research it looks like you won't be able to sum this up in a yt reply. And i think i just answered it by looking at your class module video...

[@Excelmacromastery]

Do you mean if you type cls it fills out the class name? You can use Ctrl + Space to autocomplete.

[@MaydayAggro]

@@Excelmacromastery I figured it out. I have never used or created class modules before (and I hadn't watched the tutorials on classes before this), so I didn't understand them. I still don't really, to be honest.

[@Inosix]

How does the IndexOf function compare when attempting to find a single string? If I have a string I am meant to find in an array of 5000 items, currently I am cycling through the 5000 item array, one at a time, to locate the string (if it exists). I then repeat this 1500 times (once for each item in another array). If I find the item I am looking for, I return the current array number and move to the next item in the list of 1500. Although this functions, due to the sheer number of times I am repeating the same action, I am looking to optimize this. IndexOf looks promising (even if it only saves a little time). I cannot test this myself because the links to the classes do not appear to work. **** As I typed this I attempted Microsoft Edge browser. Edge worked, Chrome didn't.

[@lonnieo4676]

oh, please, please, please... monstercampaigns isnt opening... i neeeeed this... you make so easy, i can't believe it...

[@Excelmacromastery]

Remove any popup blockers

[@lonnieo4676]

@@Excelmacromastery oh, tyvm,,, you should charge, you'd be millionaire,,, lol,,, i have yet 300 people to look at my video and i advertise on FB...

[@mayk3430]

i can't open the Source Code for some reason :(

[@kcthedog6444]

thanks so much for your channel and help. I joined to get access to all the cool stuff cls2Array2D is cool. helped solved a buch of problems. But i can't seen to alter an individual element of the new array Lets say A1:B4 = {1,2;3,4;5,6;7,8} DIM arr as New clsArray2D DIM result as double arr.data = Range("A1:B4").value result = "hello" arr.data(1,1) = result but the data @ arr.data(1,1) never gets altered what gives?

[@kcthedog6444]

typo - should have been DIM result as string. in any case still doesn't work

[@Excelmacromastery]

When you retrieve the array you are getting a copy of it. VBA copies arrays when moving between variables except when passing as a parameter which must be ByRef. I cannot remember if there is a class property for updating an individual position but if not you can easily add one that updates the class array.

[@rainerleichter]

This download link does not work for me. No matter what pops or blocks, Paul. But fine, that you can get it for yourself :)

[@rainerleichter]

Now, magic happened. I got it twice. Thanks twice!

[@TimePasser]

I can't download the files.. after keying in my email, nothing happened

[@Excelmacromastery]

Send me an email and I will take a look for you.

[@LuffyFA]

Hi! I haved the same problem, what i did is trade the browser(opera) to the Microsoft Edge, after this the page worked.

[@TimePasser]

@@LuffyFA thank you .. I will try that too. His class filter module is exactly what I'm looking for. Arrays are fast. It helped me alot at my work. Here is a suggestion for the next video .. how to make dashboard with graphs using arrays and advanced filter .. dynamic.. hahahaha..

[@saidtaliby]

Disable Ublock Origin or your other adblocker and reload the page, it worked for me.

[@johnhoel3768]

The opt in form does not work. Permanent sleep.

[@Excelmacromastery]

It's probably caused by a pop up blocker like UBlock Origin. Try using an incognito window and see if that works. If not, send me an email.

[@johnhoel3768]

@@ExcelmacromasteryI tried another browser, got past that problem, next screen said I would receive an email shortly with the download, that was about 7 hours ago, no email yet. How long does this usually take?

[@PernambucoMeuPaisOficial]

Array transpose