How To Use Excel VBA Code – Real Example

How To Use Excel VBA Code To Speed Up Manual Work – Real-World Example

Click here to download the files from the series.

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…

Part 1 – Planning and Looping Through Worksheets

In part 1, we consider the task at hand and complete a simple task that moves us in the right direction. This may seem to lack ambition, but it is important to plan any VBA task and to move forward in easy steps; if you simply jump into the VBA editor, you are likely to get into trouble. We have all been in a coding tangle before!


We know we have to take data from four sheets in the file. So, a sensible first step would be to set up a mechanism to loop through sheets in the file. It’s simple enough, and it sets us off in the right direction, and allows us (and our client?) to build trust and confidence. In the video, Chris uses a particular sheet referencing technique (using sheet index numbers) that allows us to quickly exclude sheets from the loop – since some of the sheets in the file do not contain data. We also look at how to set up the loop to cater for additional sheets in the file – remember our solution has to be flexible and scaleable.

With this relatively simple task complete, we are on our way! See you in part 2. Don’t forget to let us know in the comments how you get on, Chris will get back to you.

Part 2 – Dynamic Range Referencing and With … End with


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.

Part 3 – Loop within Loop and Countif


In the third video, we first implement a ‘loop within a loop’ – the powerful mechanism that allows us to get the first part of this job done. Conceptual thinking always comes first, so what exactly are we trying to do, and can we explain it clearly in normal language? Let’s try…

We already have one loop set up which loops through the worksheets in the file. The next loop, which operates ‘one level down’ and is embedded within the first, will loop through the name cells on each sheet. With the conceptual understanding clear, we can move onto the programming.