Excel VBA Position Control Mastery
EXCEL VBA POSITION CONTROL IN 13 EASY MACROS
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.
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.
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).
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.
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.
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…
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.
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!
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.C5ount 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 …
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.
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 …
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.
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.
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!
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.