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
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
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!
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
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 TH-cam video
I love this comment because it reminds me of the first time I discovered this! Thanks for the offer, how could I refuse? I'll let you know if I make it over, thanks!
I have been relying on TH-cam 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 TH-cam. Thanks so much for sharing your expertise with everyone!
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.
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.
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!
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.
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!
@@WiseOwlTutorials I knew that. But I suppose the speech speed is usually indicative of getting more information into the tutorial. Thanks for response! I'm doing some pretty intense coding this weekend, and I'm more of a matlab guy, so nice to know I might get a response if I have a question. Thanks!
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
***** Andrew, if you have a moment. How did you get started in VBA? I think I noticed you were a Biologist. I'm just interested in best paths taken to become as proficient as possible. Would this be to build applications and learn along the way (i.e have a problem to solve). I'm currently learning by solving problems as they arise, but going through your videos, you kind of realise that there are more efficient ways of coding. Regards Rob
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
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.
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
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.
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.
@@WiseOwlTutorials Shocked that you replied I was expecting a fellow viewer to enlighten me. However as you did can I ask another question (BTW I stumbled on the Array video, working my way through all your vids now). When you declare an array why use (0 to 9, 0 to 8) can't you use (9,8) like in old BASIC. I'm a 50 yr old ex sinclair, BBC novice programmer(very losely). I'm trying to create a sudoku /helper if that helps you understand where I'm coming from. Ps if I declare an array can I examine how many empty cells in board empty? Dim array for empty cells (num) capture address, work through possible vals. If I say cell (3,2) can be 6, 9, can I do a count of possibilities? Eg if possibilities =1 then fill cell. I don't expect you to build this for me I'm just posing scenarios for you to do another brilliant video. Kind regards Craig
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.
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?
Elaine McKenzie It's just a good practice, this will work by selecting the same starting cell (A3). But it's a good practice to know where the actual starting point of your list is.
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..
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.
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.
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
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?
Good evening, Is there a way when you add items to an array to also know the cell address from whence they came? I am adding dates to an array, then I need to find the oldest date ( which I haven't figured out yet either, min() is returning a 0 for some reason it appears to be pulling a time of 12:00:00 from a date formatted cell) at any rate, when I find the oldest date, I then need the cell address of where it was pulled from to finish out my action...thoughts? TIA David
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?
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
Might sound like a daft question, In the 'Changing the Array Size (11:26)' section, how does VBA know the counter variable is 0 when it's not set as 0 when starting?
Hi Andrew, thanks for this great tutorial! I'm having a little trouble abstracting the following line of code (time 28:58): Dimension1 = Range("A3", Range("A2").End(xlDown)).Cells.Count / Is there a specific reason why you are using Range A3 AND Range A2? Wouldn't this code also work? Range("A3").End(xlDown).Cells.Count / Thank you for your explanation!!
Hi Marcel! If you do this as you suggested: Range("A3").End(xlDown).Cells.Count You'll receive a count of 1. It counts only the cell at the end of the list starting in A3 and moving downwards. This code: Range("A3", Range("A2").End(xlDown)).Cells.Count counts all the cells in the range starting in cell A3, and extending down to the cell at the end of the list moving downwards from A2. I used A2 to handle the possibility that your list may contain only one item. If your list contains more than one item you can safely do this: Range("A3", Range("A3").End(xlDown)).Cells.Count Or you could do this: Range("A3", Range("A1048576").End(xlUp)).Cells.Count Or lots of other variations! I hope that helps!
Hi When you create a dynamic array can you only use .End(xlDown ) and .End (xlToRight) I would like to use this for a spreadsheet that was missing rows. I use something like LastRow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row To find the last populated row, but I am having difficulty getting anything to work - other than xldown which misses some of the data. Cheers
I have tried this on a very large set of data 92 columns and 15000 rows, I get to the last line but then get a mismatch error when the code tried to paste the array data into the new worksheet, I believe this is because some cells in the same columns have mixed types i.e. empty/ dates/strings. I got around this by declaring the array as a string but this doesn't really meet my needs. Any idea how I could get over this? Thanks
I created first button and copy paste it to the Userform but it did not Prompt: "You already have a control Name XXX. Do you want to create a control array?" so my question is how do I create Control array of Command buttons. what are the steps to follow? Am I missing something? I am new to this Topic Please help
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" ?
after some calculation , an array populate a column of around 300,000 rows. and delete all other array, But when copy data from array to excel, it correct data showing around 33000 rows of data , after that #NA showing . 30000 data of array now pasting in excel. Please help. (using office 2016) if read data from array only rows 65538 to excel it work file, if exceed this limit #N/A comes.
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?
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>
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?
Hi, on the 28th min you say that adding a pair of parentheses is absolutely necessary to set apart an array from a regular variable. But something like this works even without adding () Dim R R = Range("A1").CurrentRegion Can you please clarify?
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?
thanks however i'm having a problem with the last part of the video. i've copied the code exactly and the macro works perfectly when stepping through (F8), but when i run it (F5) i get a runtime error "subscript out of range"...any ideas how to resolve it?
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.
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"
I am sorry, I found a simple way. It is just to move ActionCounter = ActionCounter + 1 below lines of Loop that populates the array. Then to change counters from (1 to 5) to (0 to 4). And finally before transposing array, to test if ActionCounter = 0 (if yes, then e.g. exit sub). I found that once dynamic variant array is initiated, it is never Empty again.
Hi, I need a Help for the Excel Array function to use in VBA The Function is {=LARGE(IF('Sheet1'!$D$8:$D$47="Team 1 ",'Sheet1'!$G$8:$G$47),1)}, How can I get the result through VBA? I need the result as Large Number form the Team 1 Sale.
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
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!!
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 great videos and crystal clear explanation. Could please 🙏 solve the this error Dim j as variant, J= Array(cells(1, 10), cells(1, 20)). When watch this code in local window getting typescript error could please help me out of error.
Hi Naidu, the code works with no problem (except for the comma at the end of your variable declaration). I'm not sure why you're experiencing an error, sorry.
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...
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?
Hello, I just had a question, is there a way to populate an array in one go?e.g. insert data in excel that has 7 columns and 200 rows of data rather than looping through? Would doing it in a 'bulk' method run any faster than a loop method?Hope this makes sense!
Dear friend and teacher, I need to do an array of an undefined element and the array isn't "square". I will have a title row of criteria, and I don't know how many criteria it would be. For each criteria, it depends on what we will fill. I want to build something to have every possible element of a product. For example, I can have criteria1 (car, van, SUV, berlin), criteria2 (automatic transmission, manual transmission), criteria3 (4 cylinder, v6, v8), etc .... and at the end I would like to retrieved every possible combinaison, ( car automatic transmisison 4 cylinder, car automatic transmission v6, car automatic transmission v8) that I would paste on a different spreadsheet. I don't know if you can guide me on how doing it. Maybe there's something better than array maybe dictionnary but I can't tell thanks for your help
Hello, I have another question. Is it possible to populate the first X amounts of values in an array?Say I wanted to populate the first 10 rows of the first column of a 100 x 100 array without using a loop? I plan to do this to repeat this many times and more than once a month so I was hoping populating the first 100 rows without a loop would make the process faster! Thanks!
Will this allow me to populate the first 10 rows of an array, redimensionalise it to have twenty rows the populate the next 10 rows of data from elsewhere in the worksheet?
The 'preserve' code will not work for me when I redim. I've build the code and excel sheet identical to the ones in the video. Is there some sort of setting that might be switched off for me that doesnt allow me to use the 'preserve' code? Any other tip is also welcome, thx!
Hey wiseowl, every time i try populating my array with values from another worksheet, it ends up creating an array based on values withing the worksheet on which I am coding. I have tried activating my desired sheet before inputting my commands, but somehow it keeps selecting the values from the wrong sheet. I tried including the worksheet object in my 'range' command, but that gives me a run-time error. Is there something i am missing here?
In your example code around minute 44, FilmLenghts is a 1 dimensional array yet in the code you used the construct Int(FilmLenghts(Counter, 1)/60). That should fail, because the FilmLenghts array is 1-dimensional. Or am I missing something here?
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
Thanks Thomas!
Thanks
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
I'm so happy to hear that the videos have helped you in your work! Thanks for watching and taking the time to leave a comment, I appreciate it!
@@WiseOwlTutorials no problem at all :) i'm so excited to watch more of your videos to further simplify tasks for my department at work !
@@WiseOwlTutorials thank you! We love you
@@7Denial7 Thank you Artem!
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!
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
You're very welcome! I'm glad that you found it useful and thank you for watching!
BrIlliant material ❤ thank you sir!
Thank you for watching!
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 TH-cam video
I love this comment because it reminds me of the first time I discovered this!
Thanks for the offer, how could I refuse? I'll let you know if I make it over, thanks!
I have been relying on TH-cam 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 TH-cam. Thanks so much for sharing your expertise with everyone!
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.
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.
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!
Very productive tutorial
Thanks for watching!
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!
I have watched many instructional videos on TH-cam, and without a doubt, yours are the best. I can't thank you enough.
Great tutorial not only for begginers but also for those more advanced in VBA :)!
Thank you, Anna!
By far the best VBA tutorial I've ever watched.
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!
I wonder why would someone dislike an educational video.
Thanks brother, by the way.
i sow all your videos, its my main source for learning, after i search the web many days .
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.....what an amazing collection of tutorials. I have really enjoyed learning from all your videos, and watched them many times now.
i've always had trouble understanding arrays dimensions.
you explained it so clearly, i think i finally understood. amazing! thank you so much!
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!
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.
I love how you talk fast. I can't stand waiting for others to finish their sentences.
@@WiseOwlTutorials I knew that. But I suppose the speech speed is usually indicative of getting more information into the tutorial. Thanks for response! I'm doing some pretty intense coding this weekend, and I'm more of a matlab guy, so nice to know I might get a response if I have a question. Thanks!
Thank you sooo much Wise owl. You've helped me so much to understand vba better
man i never get bored listening to your videos :)
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
***** Andrew, if you have a moment. How did you get started in VBA? I think I noticed you were a Biologist. I'm just interested in best paths taken to become as proficient as possible. Would this be to build applications and learn along the way (i.e have a problem to solve). I'm currently learning by solving problems as they arise, but going through your videos, you kind of realise that there are more efficient ways of coding. Regards Rob
Thank you! Your videos are great. You save my day... You deserve a medal
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
28 down, 74 to go :-D And each has revolutionized some aspect of my work.Greetings and thanks from Namibia
I loved the neat trick at 33:00 !
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.
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
Andrew, You make the concepts so clear ! Thank you WiseOwl :)
I have really learnt a lot of useful skills from you. Great teaching skill!
Hello Andrew .......It was awesome. Got to know many things in arrays.
Cheers !!!! Thanks.
Sir, you are AWSOME! A great explanation... best regards from Portugal...
Superb videos :) Thanks so much. U r helping all excel enthusiasts so much
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.
Thank you Andrew :) Learning about array dimensions is helpful for my work.
Hi WiseOwlTutorials, thank you for sharing you knowledge. It is really helping me.
So many useful tips in One video!
Thank you, Peter!
Dear Andrew, great stuff !!! Thanks a lot. Greetings from Germany
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.
@@WiseOwlTutorials
Shocked that you replied I was expecting a fellow viewer to enlighten me. However as you did can I ask another question (BTW I stumbled on the Array video, working my way through all your vids now). When you declare an array why use (0 to 9, 0 to 8) can't you use (9,8) like in old BASIC. I'm a 50 yr old ex sinclair, BBC novice programmer(very losely). I'm trying to create a sudoku /helper if that helps you understand where I'm coming from. Ps if I declare an array can I examine how many empty cells in board empty? Dim array for empty cells (num) capture address, work through possible vals. If I say cell (3,2) can be 6, 9, can I do a count of possibilities? Eg if possibilities =1 then fill cell.
I don't expect you to build this for me I'm just posing scenarios for you to do another brilliant video. Kind regards Craig
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.
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?
Elaine McKenzie It's just a good practice, this will work by selecting the same starting cell (A3). But it's a good practice to know where the actual starting point of your list is.
or to use this: e.g "abc = WorksheetFunction.Subtotal(3, Columns(1))" or rows(1) but it's good if you don't have any data below table
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..
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.
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.
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
You are, as always, very welcome Pradeep!
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?
Good evening,
Is there a way when you add items to an array to also know the cell address from whence they came? I am adding dates to an array, then I need to find the oldest date ( which I haven't figured out yet either, min() is returning a 0 for some reason it appears to be pulling a time of 12:00:00 from a date formatted cell) at any rate, when I find the oldest date, I then need the cell address of where it was pulled from to finish out my action...thoughts?
TIA
David
This is great, much simpler than what i was working on.... what if the array needed to be dynamic was added to?
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?
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 all your effort.
You're very welcome, George! Thank you for watching!
i love how you said genre for twilight was "awful"
I like twilight :(
Another great video. Thanks a ton for putting it together!
Might sound like a daft question, In the 'Changing the Array Size (11:26)' section, how does VBA know the counter variable is 0 when it's not set as 0 when starting?
Hi Qasim! VBA initializes all variables to a default starting value - numbers are always 0 by default.
I hope it helps!
@@WiseOwlTutorials That explains it, thank you!
@@qasimawan3568 You're welcome!
your videos are incredible, from the content to the production value. i'm obsessed.
Amazing tutorial for beginner, many thanks!
I donated! So worth it. Thanks again.
Excellent course !
Hi Andrew, thanks for this great tutorial! I'm having a little trouble abstracting the following line of code (time 28:58): Dimension1 = Range("A3", Range("A2").End(xlDown)).Cells.Count / Is there a specific reason why you are using Range A3 AND Range A2? Wouldn't this code also work? Range("A3").End(xlDown).Cells.Count / Thank you for your explanation!!
Hi Marcel!
If you do this as you suggested:
Range("A3").End(xlDown).Cells.Count
You'll receive a count of 1. It counts only the cell at the end of the list starting in A3 and moving downwards.
This code:
Range("A3", Range("A2").End(xlDown)).Cells.Count
counts all the cells in the range starting in cell A3, and extending down to the cell at the end of the list moving downwards from A2.
I used A2 to handle the possibility that your list may contain only one item. If your list contains more than one item you can safely do this:
Range("A3", Range("A3").End(xlDown)).Cells.Count
Or you could do this:
Range("A3", Range("A1048576").End(xlUp)).Cells.Count
Or lots of other variations!
I hope that helps!
@@WiseOwlTutorials Thank you very much, Andrew, for taking the time.
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 Nader! If I get chance to go back and watch the video I'll let you know 😀
Hi
When you create a dynamic array can you only use .End(xlDown ) and .End (xlToRight) I would like to use this for a spreadsheet that was missing rows. I use something like
LastRow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
To find the last populated row, but I am having difficulty getting anything to work - other than xldown which misses some of the data.
Cheers
I have tried this on a very large set of data 92 columns and 15000 rows, I get to the last line but then get a mismatch error when the code tried to paste the array data into the new worksheet, I believe this is because some cells in the same columns have mixed types i.e. empty/ dates/strings. I got around this by declaring the array as a string but this doesn't really meet my needs. Any idea how I could get over this? Thanks
I created first button and copy paste it to the Userform but it did not Prompt:
"You already have a control Name XXX. Do you want to create a control array?"
so my question is how do I create Control array of Command buttons. what are the steps to follow? Am I missing something? I am new to this Topic Please help
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" ?
after some calculation , an array populate a column of around 300,000 rows. and delete all other array, But when copy data from array to excel, it correct data showing around 33000 rows of data , after that #NA showing . 30000 data of array now pasting in excel. Please help. (using office 2016) if read data from array only rows 65538 to excel it work file, if exceed this limit #N/A comes.
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?
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>
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?
Brilliant as usual.
If arrays are not erased, do they accumulate in the computer's memory ultimately causing performance issues?
Hi, on the 28th min you say that adding a pair of parentheses is absolutely necessary to set apart an array from a regular variable.
But something like this works even without adding ()
Dim R
R = Range("A1").CurrentRegion
Can you please clarify?
Haha! So should I conclude the parentheses aren't absolutely necessary? Or What?
Absolutely Awesome!!!
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?
thanks however i'm having a problem with the last part of the video. i've copied the code exactly and the macro works perfectly when stepping through (F8), but when i run it (F5) i get a runtime error "subscript out of range"...any ideas how to resolve it?
never mind, i changed Sheet1.Activate to Worksheets("Sheet1").Activate and it's working fine now
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.
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"
I am sorry, I found a simple way. It is just to move ActionCounter = ActionCounter + 1 below lines of Loop that populates the array. Then to change counters from (1 to 5) to (0 to 4). And finally before transposing array, to test if ActionCounter = 0 (if yes, then e.g. exit sub). I found that once dynamic variant array is initiated, it is never Empty again.
Hi, I need a Help for the Excel Array function to use in VBA
The Function is {=LARGE(IF('Sheet1'!$D$8:$D$47="Team 1 ",'Sheet1'!$G$8:$G$47),1)}, How can I get the result through VBA?
I need the result as Large Number form the Team 1 Sale.
Is it possible to use arrays to copy from 1 workbook to another?
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
Hi, may I ask if it is possible to make a 3D array into a 1D and 2D combined?
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!!
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, thanks for explaining arrays. I have a question. How do I apply array stored values to filter pivot table? Please advise. Thank you.
Hi great videos and crystal clear explanation.
Could please 🙏 solve the this error
Dim j as variant,
J= Array(cells(1, 10), cells(1, 20)).
When watch this code in local window getting typescript error could please help me out of error.
Hi Naidu, the code works with no problem (except for the comma at the end of your variable declaration). I'm not sure why you're experiencing an error, sorry.
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...
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?
Hello, I just had a question, is there a way to populate an array in one go?e.g. insert data in excel that has 7 columns and 200 rows of data rather than looping through? Would doing it in a 'bulk' method run any faster than a loop method?Hope this makes sense!
Disregard my last comments looks like you answered the question in the video!
Dear friend and teacher,
I need to do an array of an undefined element and the array isn't "square". I will have a title row of criteria, and I don't know how many criteria it would be. For each criteria, it depends on what we will fill. I want to build something to have every possible element of a product.
For example, I can have criteria1 (car, van, SUV, berlin), criteria2 (automatic transmission, manual transmission), criteria3 (4 cylinder, v6, v8), etc .... and at the end I would like to retrieved every possible combinaison, ( car automatic transmisison 4 cylinder, car automatic transmission v6, car automatic transmission v8) that I would paste on a different spreadsheet. I don't know if you can guide me on how doing it.
Maybe there's something better than array maybe dictionnary but I can't tell
thanks for your help
Hello, I have another question. Is it possible to populate the first X amounts of values in an array?Say I wanted to populate the first 10 rows of the first column of a 100 x 100 array without using a loop? I plan to do this to repeat this many times and more than once a month so I was hoping populating the first 100 rows without a loop would make the process faster! Thanks!
Thank you for the help I will try this tomorrow! As always great video!
Will this allow me to populate the first 10 rows of an array, redimensionalise it to have twenty rows the populate the next 10 rows of data from elsewhere in the worksheet?
The 'preserve' code will not work for me when I redim. I've build the code and excel sheet identical to the ones in the video. Is there some sort of setting that might be switched off for me that doesnt allow me to use the 'preserve' code? Any other tip is also welcome, thx!
***** I'm getting a runtime error 13: type mismatch.
***** It does yes. I'm not at my PC at the moment but I'll post it later. Thx!
Hey wiseowl, every time i try populating my array with values from another worksheet, it ends up creating an array based on values withing the worksheet on which I am coding. I have tried activating my desired sheet before inputting my commands, but somehow it keeps selecting the values from the wrong sheet. I tried including the worksheet object in my 'range' command, but that gives me a run-time error. Is there something i am missing here?
Hi all, question: Range("A3", Range("A2").End(xlDown)), why A2? Is it to handle the case when there is only one movie in A3? Thanks!
Love your videos!
In your example code around minute 44, FilmLenghts is a 1 dimensional array yet in the code you used the construct Int(FilmLenghts(Counter, 1)/60). That should fail, because the FilmLenghts array is 1-dimensional. Or am I missing something here?
Got it. The default behavior of the dynamic array was was the missing piece. Thanks.
Is there a way to find the length of an element of an array? Like len(arr(1,2)) =3 when arr(1,2)="abc"
***** Thank you, I will try it by myself