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.
Click here to download the file used in the video.
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.