How to Use Excel VBA Code - Real Example P2

How can we use Excel VBA code to speed up manual work in Excel? In this series we explore an example Excel VBA project sent in by Eric, a viewer of the channel. We aim to build an Excel VBA application to move data around a file at the 'click-of-a-button', saving hours of frustrating manual work. The application should be flexible and scaleable enough to handle changes in business circumstances - a challenge indeed! Let's get into it...

Download File Link:
---------------------------------------------------------
In part 2, we create a mechanism to loop through the data on each worksheet. Looping through 'static' data is simple enough - we have done that in other videos on the channel - but Eric's briefing presents an additional challenge: how to loop through a variable amount of data?

What is meant by 'variable' in this case? As you can see in the download file, each sheet contains a different number of rows; further, Eric wishes to be able to add new sheets with any number of rows. What is required is a solution that can handle these different situations without the need for additional coding; what is needed is a *dynamic* solution.

The video explores how to create such a solution. We learn about the powerful .end(xldown) construct which selects to the end of a dataset and drives the dynamic mechanism. We use the 'step into' facility in the VBA editor to work through the code whilst looking at the Excel window to understand how it works, and to test the new mechanism.

Finally, we look at how to integrate With .... End With to simplify the code and to avoid having to select sheets. Selecting objects such as sheets and cells is a 'direct referencing' approach; routines built in this way can take some time to execute, and there is a more efficient approach.

We would much rather have a fast routine built on 'remote' referencing; ie. a routine that does not select sheets but uses sophisticated referencing to get the job done faster.

See you in part 3. Don't forget to let us know in the comments how you get on, Chris will get back to you.

Series Playlist Link:

🎁YOUR EXCEL CHEATSHEET
🎁1 Page Summary Of What You Need To Know

Also on Insta :-)

[tunnelvision]

Excellent. Just discovered this and finding immensely helpful. Thanks

[mxrvxn]

Great vid. Very beginner friendly. More of this pls

[BlvdMinSS]

Chris...this is AMAZING!!!! I’m hooked! I must find a course or class on VBA. This has change my whole spreadsheet world.

[pavelnemec]

Great videos. You helped me a lot to make my excel sheets more efficient.

[TigerSpreadsheetSolutions]

I hope you got value out of part 2! Did you get the dynamic mechanism working? And have you used With ... End With before? Let me know in the comments, I will get back to you.

[MrNashfury]

Thank you, Chris! Great video, as always!

[andrewbooth]

Excellent tutorial video. I'm new to using Excel VBA, learning myself, via videos like this one and trusty trial & error. I'm looking for project ideas or completed projects that I could follow and work through, at my own pace, learning as I go. Do you know if these types of thing exist online?