Excel VBA Collections: How to Read between Collections and Worksheets (3/5)

In this Excel VBA Collections video, we see how to read from a worksheet to a VBA Collection and to read from a VBA Collection to a worksheet.

Many people get confused when trying to write out a collection to a worksheet. But it's pretty easy once you know how.

Related links:

Table of Contents:
01:20 Current Region
02:34 Reading from the worksheet
05:26 Passing a Collection to another Sub
07:31 Writing from a Collection to a worksheet
10:12 Reading the data header to the Collection

Shortcut Keys:
Ctrl + G: View the Immediate Window.
Ctrl + Shift + 8(or Ctrl + *): Get the current region on a worksheet.
F5: Run the code from the current sub.
F9(or click left margin): Add a breakpoint to pause the code.

[krn]

Thanks Paul, looking forward to the video on classes and using collections that contain multiple columns of data

[Karthickchidambaram]

Your teaching is great sir and voice too

[joaocustodio]

Paul's clarity and teaching ability is impressive.

I always learn something new.
Thanks Paul.

[houstonvanhoy]

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.

[StillChrist]

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.

[rrrprogram]

Brilliant stuff... Best VBA channel on YouTube

[steveroberts]

Another fantastic video Paul. For the example you used I prefer to use advance auto filter as it is much faster.

[sasavienne]

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... 🔆 👏 🌟

[yandhi]

Thank you! Very helpful. Looking forward to the rest of your videos

[wayneedmondson]

Hi Paul.. great video. Learning lots of new things with this one. Thanks for sharing. Looking forward to more. Thumbs up!!

[nadermounir]

Nice video 📹 u bring interesting stuff in VBA.

[BlenderZen]

That's great Paul thanks always wanted a more detailed VBA collections and worksheet tutorial!

[le_Bizarre]

Hi. Thanks very much for the video. But I didn't understand what is ShMarkB (as the sheet is called Marks Class B)?

[ricos]

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

[ukaszbrzozowski]

Hello Paul,
your video are great but I'm wondering why you're using ctrl+shift+8 to select all instead of ctrl+a ?

[hidupchannel]

Hello Master, Is that possible create collection from closed workbook?

[vijaysahal]

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

[educational]

Paul, I Wonder Why we don't just return the value to the worksheet rather must to add in collection first ?

[caulacau]

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

[michalroesler]

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?