Excel VBA Position Control Mastery


Excel VBA position control is one of the main skills of the Excel VBA programmer. Excel gives us a grid consisting of rows and columns; how well we can control position on that grid is a key determinant of the impact of our Excel VBA implementations.

Click here to download the file used in the video.

Excel VBA Position Control Thumb

From a practical perspective, position control allows us to work dynamically with datasets in Excel; that means being able to work with a dataset that might change in size – as almost all datasets tend to do in Excel! These skills will add the wow factor to your implementations – your colleagues will ask you ‘how did you do that?’

How did I determine the content for this video? I found that this bundle of position control techniques helped move me from intermediate to professional level as a programmer. I have translated them here into 13 one-line macros that might take your Excel VBA to the next level.

We’ll work from simple cell selection using a static reference all the way to dynamically selecting a range of cells. Don’t forget to leave a comment to let me know what you think. Welcome to Excel VBA Position Control Mastery!

Excel VBA Position Control Techniques Breakdown:

00:15 1. Simple Cell Selection
Let’s start with something we know – a simple cell selection with a static reference.

00:32 2. Select Last Cell In Dataset Using .End(xldown)
Here’s our first piece of magic syntax – .End(xldown) allows us to select the last cell in the dataset; in other words, the cell above the next empty row. Look out for its partners in crime .End(xlup), .End(xltoright) and .End(xltoleft).

01:16 3. Select Multiple Cells Using Range(Range,Range)
Ah – a Tiger favourite, the powerful Range(Range,Range) referencing technique to define a range of cells. We begin with static or ‘hard-coded’ references – make sure you ‘play’ with some different cell addresses to get a feel for how it works.

02:07 4. Select Multiple Cells To The End Of A Dataset
Next, we combine Range(Range,Range) with .End(xldown) to dynamically select a dataset. Yes, this is a ‘dynamic’ technique, so if entries are added at the bottom, the code still does the job. As we’ll see, the techniques create powerful synergy as they combine together.

02:44 5. Select The Last Row On A Worksheet With Simple Referencing
How many rows are there in a spreadsheet? It’s something I learned on my Masters! Here I demonstrated the tuition fees did not go to waste…

03:51 6. Use Rows.Count With A Message Box
There is no need to manually input the number of rows (or indeed to remember the number) because Excel already knows – we just have to ask Excel to tell us using VBA. Here we combine the Row object and .Count method to externalise the number of rows in the spreadsheet – make sure you impress your friends with this one.

04:28 7. Use Rows.Count To Select Last Row Dynamically
Now we can combine the previous two techniques together to select the last row dynamically – no hard-coded references needed!

04:57 8. Use Columns.Count With A Message Box
We have seen how to do this with rows – can you do it with columns? What difficulties might counting columns present that counting rows did not? Hhhhmm …

05:20 9. Use .Cells Referencing Technique
Here a new technique arrives to save the day! .Cells referencing uses numbers (as opposed to alphanumeric references) to refer to cells. Let’s understand the basic concept with some simple examples.

06:16 10. Use .Cells To Select Last Column Dynamically
We combine together the previous techniques to once again select a cell far, far away without any ‘hard-coded’ values. Now, the big finale …

06:46 11. Work From Bottom Of Worksheet To Select Last Row
The code here looks tricky but only comprises concepts we have practised above. What does it mean in a practical sense? This approach allows us to work with datasets that might have empty rows in them.

07:56 12. Work From Right Of Worksheet To Select Last Column
This approach also allows us to work with datasets that might have empty rows in them.

08:51 13. Punch The Air – Full Dynamic Dataset Selection
The actual approach I use to work with datasets dynamically using Excel VBA. Make sure you give it a try!

Were you able to follow this video? Do you feel you have achieve a measure of position control mastery in Excel VBA? Or was it a bit too much to take in? Either way, let me know in the YouTube comments, I will get back to you.


FREE: Your Complete Guide to Spreadsheet Projects (Video and PDF)
When you sign up to the Tiger mailing list (click left).

On the mailing list, in addition to the free guide above, you will receive privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases

Follow Tiger on Facebook:
Also find us on Insta

Contact us here.

This entry was posted in Advanced Articles, Beginner Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.