An excellent teacher of Excel! I have learned to hang on every word you say...every jot and tittle...every dot and title. Easy to follow ! Sign of a true master.
Hello Paul, your videos are stunning, brilliant, excellent and simply great. Your biggest advantage is that you understand the questions of beginners and you explain everything so clearly. I will recommend your channel to everyone who wants to learn VBA. Thank indeed... 🔆 👏 🌟
Hello, Sir Paul. When there is a series of videos (for example, this is 3 of 5), would you consider including the URL of the following video with your header comment. Thank you.
Very true Steve. Advanced filter is the best way to copy data in Excel VBA in most scenarios. I wrote a post about all the VBA copy methods: excelmacromastery.com/excel-vba-copy. In this video, i was using copy as a simple example to illustrate how collections and arrays differ.
To read more than one column it can also be accomplished by using coll.Add array(rg.Cells(i,1).Value, rg.Cells(i,2).Value) and so on, instead of using classes.
Just going through this series as it's always nice to see how others teach these things (I do Excel training quite regularly). You're considerably better than me at teaching it has to be said, very clear and easy to follow! Although, I'd have used a For Each rather than For i = 2 To rg.Rows.Count for its increased performance, as a great man once told me in Collections lesson 2/5
hii sir I m loving your teaching way but I hve some issue about ds collections video suppose I want to print same debog values inthe same worksheets then how can it print there what will code then
I have check the Excel shortcut key list from (support.office.com/en-us/article/keyboard-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f#PickTab=Windows). Ctrl+A or Ctrl+Shift+Spacebar: Select the current region if the worksheet contains data. Press a second time to select the current region and its summary rows. Press a third time to select the entire worksheet. Ctrl+Shift+Asterisk (*): Select the current region around the active cell. Hence, I think both are effectively the same when they are pressed once. I personally prefer Ctrl+A because it can be done with one hand easily.
Hi Paul, quick question if I may. I have always referenced worksheets by their name. Here you use something you call the "code name". What exactly is this code name and where do we find it? Many thanks Caula
It makes the code more flexible when the input(reading data) and output(writing data) are only have a collection in common. In a small application like this it doesn't matter but in a big one it can make a lot of difference.
Dear, I follow most of your videos. it was very interesting and I have learned a lot. but I get a problem with the reading collection when I add a range consists many cells as item. it makes the collection has multidimensional and I can't write its items in cells of excel. I hope you reply to me in close time. also anybody can answer me.
Hi I have one query for you. When we copy text file and paste in excel. Data splitted into different cells and too many blanks rows found. Now tell me how to delete blank rows by using VBA macros. Column range from A TO XA. More than 72000 rows.
Is 256 the limit of the items being stored in the "single column collection" or is it just the limit of items that the locals window / debugger can show?
I consider myself pretty advanced with excel/VBA and never knew about CTRL + shift + 8... also helpful to know that is how currentregion is defined. Mind blown!
In simple therms they are used to store groups of data. The big difference with arrays is that you don't need to worry about the size. The collection automatically resizes when you add an item.
Hi Mi, It is very fast and easy to copy from worksheets to arrays. But if filtering data a collection can be easier to use. Check out the next video in the series where I'll be comparing arrays to collections.
Thanks Paul, looking forward to the video on classes and using collections that contain multiple columns of data
You're welcome. The video on classes and collections will be the 5th one in the series.
@@Excelmacromastery Thanks Paul, we will be waiting with enough expectation.
@@jaimepantigoso5161 Glad to hear Jaime.
Your teaching is great sir and voice too
Thanks and welcome
An excellent teacher of Excel! I have learned to hang on every word you say...every jot and tittle...every dot and title. Easy to follow ! Sign of a true master.
Thanks!
Paul's clarity and teaching ability is impressive.
I always learn something new.
Thanks Paul.
you're welcome Joao
Brilliant stuff... Best VBA channel on TH-cam
Hello Paul, your videos are stunning, brilliant, excellent and simply great. Your biggest advantage is that you understand the questions of beginners and you explain everything so clearly. I will recommend your channel to everyone who wants to learn VBA. Thank indeed... 🔆 👏 🌟
Glad to hear it Salim. Thanks very much for your support.
Hello, Sir Paul. When there is a series of videos (for example, this is 3 of 5), would you consider including the URL of the following video with your header comment. Thank you.
Thank you! Very helpful. Looking forward to the rest of your videos
Another fantastic video Paul. For the example you used I prefer to use advance auto filter as it is much faster.
Very true Steve. Advanced filter is the best way to copy data in Excel VBA in most scenarios. I wrote a post about all the VBA copy methods: excelmacromastery.com/excel-vba-copy.
In this video, i was using copy as a simple example to illustrate how collections and arrays differ.
perfect tutorial
Nice video 📹 u bring interesting stuff in VBA.
That's great Paul thanks always wanted a more detailed VBA collections and worksheet tutorial!
No problem at all
Thank you very much.
To read more than one column it can also be accomplished by using coll.Add array(rg.Cells(i,1).Value, rg.Cells(i,2).Value) and so on, instead of using classes.
Hi Paul.. great video. Learning lots of new things with this one. Thanks for sharing. Looking forward to more. Thumbs up!!
thanks for all the feedback Wayne. It's good that you're getting so much from the videos.
Thank you really like your channel
Just going through this series as it's always nice to see how others teach these things (I do Excel training quite regularly). You're considerably better than me at teaching it has to be said, very clear and easy to follow!
Although, I'd have used a For Each rather than For i = 2 To rg.Rows.Count for its increased performance, as a great man once told me in Collections lesson 2/5
Thanks Rico.
Brilliant video paul
Thanks Mak
very useful, thanks.
glad you enjoyed it
Hi. Thanks very much for the video. But I didn't understand what is ShMarkB (as the sheet is called Marks Class B)?
It's the codename of the worksheet.
hii sir I m loving your teaching way but I hve some issue about ds collections video suppose I want to print same debog values inthe same worksheets then how can it print there what will code then
Hello Master, Is that possible create collection from closed workbook?
awesome
Hello Paul,
your video are great but I'm wondering why you're using ctrl+shift+8 to select all instead of ctrl+a ?
Because Ctrl+* is CurrentRegion and Ctrl+A is Select all.
Excel Macro Mastery Hmm, I have always used ctrl+a and it worked as expected selecting current region 🤔 There are some differences between them?
I have check the Excel shortcut key list from (support.office.com/en-us/article/keyboard-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f#PickTab=Windows).
Ctrl+A or Ctrl+Shift+Spacebar:
Select the current region if the worksheet contains data. Press a second time to select the current region and its summary rows. Press a third time to select the entire worksheet.
Ctrl+Shift+Asterisk (*):
Select the current region around the active cell.
Hence, I think both are effectively the same when they are pressed once.
I personally prefer Ctrl+A because it can be done with one hand easily.
Hi Paul, quick question if I may. I have always referenced worksheets by their name. Here you use something you call the "code name". What exactly is this code name and where do we find it?
Many thanks
Caula
Paul, I Wonder Why we don't just return the value to the worksheet rather must to add in collection first ?
It makes the code more flexible when the input(reading data) and output(writing data) are only have a collection in common. In a small application like this it doesn't matter but in a big one it can make a lot of difference.
Dear, I follow most of your videos. it was very interesting and I have learned a lot. but I get a problem with the reading collection when I add a range consists many cells as item. it makes the collection has multidimensional and I can't write its items in cells of excel. I hope you reply to me in close time. also anybody can answer me.
Hi
I have one query for you. When we copy text file and paste in excel. Data splitted into different cells and too many blanks rows found. Now tell me how to delete blank rows by using VBA macros. Column range from A TO XA. More than 72000 rows.
Is 256 the limit of the items being stored in the "single column collection" or is it just the limit of items that the locals window / debugger can show?
I consider myself pretty advanced with excel/VBA and never knew about CTRL + shift + 8... also helpful to know that is how currentregion is defined. Mind blown!
Thanks David. The CurrentRegion is very useful. I was using VBA for a long time before I knew about it.
what is the signicance of using a “Collection” in vba code? newbie here..thanks for any reply
In simple therms they are used to store groups of data. The big difference with arrays is that you don't need to worry about the size. The collection automatically resizes when you add an item.
Paul, how do collections compare with arrays when it comes to reading from the worksheet?
Hi Mi, It is very fast and easy to copy from worksheets to arrays. But if filtering data a collection can be easier to use. Check out the next video in the series where I'll be comparing arrays to collections.
Thanks Paul, I’ll check it out
I have a problem. I can not import the module by this way: File->Import File ...( this not working in VBA excel). Pls help me. Thanks
Which file are you importing?
@@Excelmacromastery the file not important, i want import bas file but this are not show dialog box.