For Efficient Reference: What You'll Learn 00:11 What is an Array? 00:42 Declaring a Fixed Size Array 01:30 Using the Option Base Statement 02:24 Declaring the Lower and Upper Bounds 03:11 Populating an Array 04:07 Reading from an Array 04:56 Erasing an Array 05:38 Looping Over an Array 07:02 The LBound and UBound Functions 11:10 Declaring Multi-Dimension Arrays 13:02 Populating a Multi-Dimension Array 15:49 Looping Over Multi-Dimension Arrays 18:31 LBound,UBound and Multi-Dimensions 22:13 Reading from Muti-Dimension Arrays 24:57 Dynamic Arrays and ReDim 26:59 Writing a Range to a Dynamic Array 31:44 Erasing Dynamic Arrays 34:16 Writing a Dynamic Array to a Range 35:25 Preforming Calculations in Arrays 38:24 Outputting Answers in New Range of Cells 45:39 Resizing Arrays Dynamically 47:25 LCase Function 49:26 Preserving the Content of Array (ReDim Preserve) 54:20 Transposing an Array (application.transpose) 57:31

Wise Owl for President! I can’t believe the teaching quality and methodological rigor of this series. Every chapter is a winner. One of my absolute favorite goto VBA sources. Bravo!

31:44 is hilarious. COMPLETELY over the top *Tearfully shreds all my notes up to now* These are amazing videos, you are single handedly doubling my work productivity. If you are ever in Dublin, there is a pint owed. I have never learned so much from a KZfaq video

WiseOwl, you helped teach me how to implement a macro at my work that saved hours of work each month (getting praise from my boss + departments) - thank you for your crystal clear and simple tutorials!!! soo appreciative of your work here

Since I am new to this field, I took a week to understand this lecture. Currently, I am almost certain that I can write a book for this lecture due to the comprehensiveness of this lecture, which explains the array. Really so so so so thanks

Thank you for explaining the Arrays in relation to the "dimensions" of the Sheets in excel. It is such an elegant way to describe what is happening in the script language. And i have never seen yet a better way to use (and explain) the expressions window.

Wow! You know that feeling when you've found the tutorial that you've been looking for?...I just got that feeling. I landed on part 25 because I needed a refresher on arrays. I am now a subscriber and will be working through the entire series. Well done!

You just cleared up something about LBOUND and UBOUND that has plagued me for years. No one else has ever explained it that way. Thank you!

Andrew / WiseOwl, your pace and level of explanation is just perfect. I write very large models for supply chain optimization. Speed is everything especially in large models. Using arrays as you explained is key way to make my models run amazingly fast. Thanks a ton.

Thank you so much Andrew! This is better than the VBA class I paid for!

Thank you so much for all the VBA excel tutorial you've uploaded. Each one of your material is better than any of the paid tutorial in the internet.

After watching you run through 12,000 rows yesterday in a flash using an array, I had to rewatch this one again. Love how quick you can calculate hundreds or thousands of rows in a few seconds compared to an individual for each loop. Thanks Andrew.

Wow.....what an amazing collection of tutorials. I have really enjoyed learning from all your videos, and watched them many times now.

I have watched many instructional videos on KZfaq, and without a doubt, yours are the best. I can't thank you enough.

your videos are incredible, from the content to the production value. i'm obsessed.

By far the best VBA tutorial I've ever watched.

I have been relying on KZfaq when it comes to learning such as programming and high level mathematics. But this is by far the best learning experience that I have encountered so far. Not to mention that this is my very first reply on KZfaq. Thanks so much for sharing your expertise with everyone!

Andrew, must say this is just what I was looking for. I've spent ages looking through forums, books etc to get a grasp of arrays! I'll look no further, this is Top Drawer Stuff Many thanks for posting

i've always had trouble understanding arrays dimensions. you explained it so clearly, i think i finally understood. amazing! thank you so much!

You sir are a life saver ! I knew what I was doing was possible somehow and you just nudged it home! Very good instructions! Thank you!

Andrew, You make the concepts so clear ! Thank you WiseOwl :)

Great tutorial not only for begginers but also for those more advanced in VBA :)!

Thank you! Your videos are great. You save my day... You deserve a medal

i sow all your videos, its my main source for learning, after i search the web many days .

Thank you sooo much Wise owl. You've helped me so much to understand vba better

Your right, my confusion was overlooking the fact that using -1 caused results to be Dimension1(0 to12,0 to 4) instead of Dimension1(1 to 13, 1 to 5). Thanks again for your powerful and engaging series!

I love how you talk fast. I can't stand waiting for others to finish their sentences.

I have really learnt a lot of useful skills from you. Great teaching skill!

Very helpful video. Arrays have always been difficult for me and your video finally made "the light bulb go off". LOL it finally makes sense. Thanks.

28 down, 74 to go :-D And each has revolutionized some aspect of my work.Greetings and thanks from Namibia

man i never get bored listening to your videos :)

Hi WiseOwlTutorials, thank you for sharing you knowledge. It is really helping me.

Another great video. Thanks a ton for putting it together!

Dear Andrew, great stuff !!! Thanks a lot. Greetings from Germany

Hello Andrew .......It was awesome. Got to know many things in arrays. Cheers !!!! Thanks.

I wonder why would someone dislike an educational video. Thanks brother, by the way.

Thank you Andrew :) Learning about array dimensions is helpful for my work.

Sir, you are AWSOME! A great explanation... best regards from Portugal...

Superb videos :) Thanks so much. U r helping all excel enthusiasts so much

Excelent, Your videos are awesome, thank you very much, I learned arrays thanks to you. but using irregular dynamic arrays in a loop is very complicated, actually ı dont know that if it is possible, so I also wonder very much irregular or jagged arrays especially for your "resizedynamicarray example" ?

So many useful tips in One video!

Amazing tutorial for beginner, many thanks!

i love how you said genre for twilight was "awful"

I donated! So worth it. Thanks again.

49:20 where you talk about case there is also a side point about a variable instead of = that enables wildcards. Instead of: Value = "action" Value Like "*ctio*" So "action" will be detected but also any film criteria containing ctio so "action film" will be picked up. So you are catching similar criteria, if multiple people are inserting different but synonymous terms.

thank you sir, I am fan of yours. I am vba developer. upload more videos like this.

Excellent course !

Hi Andrew, Awesome learning videos thankyou very much. Is there a way to Redim an array while preserving but using the "quick" method? I need to redim preserve a large array but then add in lots of data as i loop through sheets. I'm after efficiency as i'm turning the sub into a function and right now it's too slow. The end of your video described the redim preserve but it was looping through each cell in the sheet range.

Thank you for all your effort.

Better than most. Yet since this is an introduction, it would have been very helpful if you had run your VBA statements after each change so that we could see what that does.

Hi, thanks for explaining arrays. I have a question. How do I apply array stored values to filter pivot table? Please advise. Thank you.

Unbelievable nice tutorial. Thank you so much.

Hi , Very nice Explanation and learnt a lot. We can use dynamic variables as lastrow and lastcolumn to loop across rows and columns instead of LBound and UBound. See the below code I have used: Dim a, b As Integer Sheet1.Activate Range("a1").Activate a = ActiveCell.Cells(Rows.Count, 1).End(xlUp).Row - 1 b = ActiveCell.Cells(1, Columns.Count).End(xlToLeft).Column ReDim toptenfilms(0 To a, 0 To b) As Variant Dim i, j As Long For i = 0 To a For j = 0 To b toptenfilms(i, j) = Range("a2").Offset(i, j).Value Next j Next i Thanks, Nitish

Hi Andrew, , great video on Arrays, keep coming back and revisit this brilliant tutorial as reference guide. I wasn’t quite sure whether to post my question within the scope of this video, but I thought to ask you whether you might have some ideas how to do the following task I am trying to accomplish: I have more than 600 workbooks in a folder containing multiple spreadsheets. Each of spreadsheets contains some sort of data which I need to access their range and copy to new workbooks. From sheets stored in one workbook for example I only need one, which is predominantly stored at Sheets index position 1, although in more than 100 workbook that is not the case. I already looped through all files in folder with Dir() and know which workbooks do have sheets on wrong index position. I need also to copy a range of that specific sheet to another workbook, which I am able to do but how to use VBA to find dynamically workbook I need and sheet by its index position. I really want to avoid opening via Dir function in a loop workbooks and do work manually. How can I dynamically use an array or any other method and find those workbooks and sheets within workbooks that I am interested and either flag them through Boolean if statement and either have msg box pop out saying: “This sheet needs work”, or just copy the range to new workbook and save it along with the remainder of workbooks in a folder. Your ideas would be much appreciated. Thanks Denin

Thank you for your tutorials, very useful and structured information. I have one question, may be it sounds stupid but i'm just curious... Is it possible to populate the arrays not only with values, but lets say with objects or with other arrays?

BrIlliant material ❤ thank you sir!

Thank you Andrew Sir 1000 times thank you so much, sir One thing I learned that if it is possible to avoid looping than avoid it because it slow the code

Brilliant as usual.

Absolutely Awesome!!!

I loved the neat trick at 33:00 !

Very helpful Video, thank you for sharing

very well explained! thank you!

Love your videos!

Thanks! This helped me a lot!

Very educative video. I have 1 question. Can you make a video in which data (in time format) is present in columns with start time and end time and this data have to be sort in ascending order...

I love these videos! I've learned so much already. I do have a question that has been confusing me since the beginning though. I'm not sure why it is that when you are selecting a dynamic range, like at 33:06, you select the top left corner (cell A3), but then when you are giving the second half of the range, you select the one above it (cell A2). I've noticed this seems to be your standard procedure, but I'm not sure why. Is there a reason you don't just use A3 for both parts?

Brilliant! Thank you very much!

Very productive tutorial

Awesome video, really helped me out a lot. I have 1 issue though. I have an array that I populate with a loop. I am preserving the array like you showed in the video to dynamically grow the array. I am attempting to transpose it to a sheet and I get a type-mismatch error. If I do not transpose, it works great. Range(Activecell, Activecell.offset(UBound(answers, 2) - 1, 15)).Value = Application.Transpose(answers) Any thoughts or help.

Hi Andrew, trust you are doing well. I need your expertise. I have different values in range which consists of positive and negative value. like 112, 36, -158, 62, 10. I need to apply a logic where the positive values when sums up and equals to the negative value -158, then the code should highlight all cells which made up to this combination like 122, 36, 10 and -158 should be highlighted in yellow color. The code should be so dynamic to handle the various positive values and keep on adding up on various combination of positive numbers until it matches the negative amount.

Oh thanks a lot.. awesome videos... thanks for sharing the knowledge with us.. :-)

Hi Andrew, Your teaching technique is excellent... I have learnt a lot from all of you videos. I have small question on the calculating the time from the length of the movie: Which one is more efficient as per you experience considering time it takes to do the job 1) using loop as you did in your previous videos 2) using this technique of arrays - where we do the calculation in Array variable Thanks, KP

Sir, your presentation method is excellent. I watch many "HOW TO" videos and I find I have to repeat the video over and over and try to disect the information from the data. You assumed nothing from the viewer, went through step by step and explained every step and the pitfalls and errors, it was so easy to see and comprehend what was going on (except for 1 thing, something like Range D3 to D2 exceldown, but another time it was D3 to D3 exceldown, can you explain the rationale? )I'm only going to give 9999.9 /1000.

This video has been enormously helpful in my understanding with VBA. At 28:53 however, I still don't understand the need for having the ...range("A2"). What is the reasoning or logic behind having this here? So when the range for Dimension1 is calculated, it counts from "A3", then..I get confused how the "A2" plays in there..

Quick question, I have an array, ranging between 10 by 15 to 80 by 120 in size, with three different formulas being calculated and inputted into the cells. I need to be able to run the formulas say 7 times, populating the array with different values each time, but in the end I need it to display the sum of the formulas for each cell. So when everything is said and done, cell C4 for example needs to show formula1+formula2+formula3+. . . How would I go about that exactly? Thank you>

Excellent. Thank you.

Hi Wise Owl, Is the array only for cells inside the excel or for even files outside excel?

I [very rarely] comment on content but I am very impressed with your video and it explained to me in excellent language that I was able to understand Keep up the good work and thank you very much, Andy

Hi Andrew, i have a question with regards to performing a simple subtraction calculation using arrays. If i have sales price and cost price and want to calculate the difference between the two (cash margin) how can i do it using arrays?

Very interesting topics found on timeline 39:00 but a clarification required on timeline 40:55 as why End Range begins from D2 while same results can be obtained by using End Range as D3 because begin range is D3. Please help.

Really thanks for this great video Andrew. I only wonder how to test dynamic array for empty. When Redim Preserve, I intentionally changed all Action films to other so ActionCounter is 0 and I seem not to test if variant array is empty using IsEmpty, IsNull, Ismissing or other functions. Even Locals show (blank) instead of "Empty"

Excellent... Thank you

Hi Dr.Owl Thank you for these awesome videos. My question is that if we can already copy and paste by using the methods, why don't we bother to use arrays? I guess that you can choose what criteria you want. Like you picked "action"? Thank you for your answers!

First of all ... thank you for the video!! I have one question .... when you create the Quick Array "TopFilms = Range("A3", Range("A2").End(xlDown).End(xlToRight))" how is it possible to read out just the Action videos from the Array and write them to a new sheet, the Fantasy in another sheet, etc....??? Is there also a short solution for that? Again ... thank you for you videos!!

Hi Wiseowl, great video. I tried running the quick dynamic array following your code video 37:17 when I get to this line of code Range(ActiveCell, ActiveCell.Offset(UBound(TopFilms, 1) - 1, UBound(TopFilms, 2) - 1)).Value = TopFilms I get the Run-time error 1004 Application-Defined or object-defined error. However if I remove Worksheets.Add and allow the data to update sheet1 it works. How do I get it to work on a new or different sheet?

Andrew, your videos are high quality and you're a very patient teacher:) This makes for an outstanding learning experience! Thank you for sharing your wisdom!!! I also wanted to ask you a question about a Compile Error: Type Mismatch that I'm receiving. In the code below, I'm receiving this error on the first < sign in the IF statement. Would you mind assisting? Thanks for your help and let me know if you need any further information. Sub CalculatingEndDateStatus() Dim DaysToCloseout() As Variant Dim Answer2() As Variant Dim Dimension2 As Long, Counter As Long Sheet2.Activate DaysToCloseout = Range("G2", Range("G2").End(xlDown)) Dimension2 = UBound(DaysToCloseout, 1) ReDim Answer2(1 To Dimension2, 1 To 2) For Counter = 1 To Dimension2 If DaysToCloseout < 0 Then Answer2(Counter, 1) = "Past End Date" ElseIf DaysToCloseout < 30 Then Answer2(Counter, 1) = "Less Than 1 Month" ElseIf DaysToCloseout < 60 Then Answer2(Counter, 1) = "Less Than 2 Months" ElseIf DaysToCloseout < 90 Then Answer2(Counter, 1) = "Less Than 3 Months" Else Answer2(Counter, 1) = "More Than 3 Months" End If Next Counter Range("H2", Range("H2").Offset(Dimension2 - 1, 1)).Value = Answer2 Erase DaysToCloseout Erase Answer2 End Sub

Thank you so much bro your really great..

Thank you for this!!

Thank you once again... maybe i should just watch the rest.

For anybody trying to redim preserve the first dimension in a multi dimension array, you have to transpose the array. Make the first dimension equal to the second dimension and change the second dimension to the number of dimensions desired then transpose again.

great work this !!!

Thank you Amdrew for this amazing video. Just a quick question in 46:13.. why are u using offset(dimension1-1,1) instead of offset(counter -1, 1) ???

HI Wiseowl You are awesome teacher, I am from India ,today am professionally settled from wiseowl, thank you so much for sharing information, Currently am looking out for MS access and access VBA, do you have any videos on this , please help us

If arrays are not erased, do they accumulate in the computer's memory ultimately causing performance issues?

Hi, maybe you could help me with a question: I have two different bidimensional arrays more or less like this: arr1(1 to 800, 1 to 45) arr2(1 to 500, 1 to 45) I want to compare each entire line (45 columns) of each array one another... how could I do that?

At 30:34 your code Dimension1 = Range(“A3", Range(“A2”).End(xlDown)).Cells.Count - 1 would give me the wrong count being 1 too high until I changed "A2" to "A3". My change seems reasonable but when you run it, you seemed to get the right results. Am I overlooking something?

Hello sir, i found these videos very helpful and learned so much but i have a query to be resolved. Sir i want to know how to update a particular column of a worksheet in real time for example stock market share prices for particular share and automatic update for the concerned chart as well. further i have also watched the video number 54 but whenever i am applying the same in my excel sheet it is not downloading any file. please also tell teach me the formula of webservice in excel 2013. Thanks

Hello. I would like to ask a situation. How can I output the store data in the array to a new worksheet but in a different order in a quick way? For example, in your excel, when out put data to a bew sheet it follow this order: 1 ,film length ,film name, blank column, xyz... How can this be done in a quick way?

Dear Andrew, i've a question on the video segment of CalculateWithArray. In this instance, you wrote a for next loop with array to compute and populate the elements of filmlength into 2 different columns (F & G) i'm looking to do something very similar, but in my current work i'm writing a nested for next loop. Bascially, i'm looking to repeat the process several time for the other columns. Right now, i'm struck (keep getting an error prompt "subscript out of range"). Are you able to provide some advice on how to overcome this?

Hi Andrew, thanks for the video. How do you manage au write your variables automatically? You just start typing TopT and it seems that TopThreeFilms is automatically written