How to use VBA ActiveCell property in Excel
By
SpreadCheaters
In today’s tutorial we’ll learn how to use the ActiveCell property in Excel through VBA. Let’s take a look at the dataset in our hands. It has an active cell marked with a green rectangle by Excel, as shown above. We will try some most common properties and methods to use this property, however, the functionality is not only limited to the methods shown in the tutorial. You may use it as per your customized requirements.
The ActiveCell property in VBA (Visual Basic for Applications) refers to the cell that is currently selected in a worksheet. There are properties and methods that come with it which allow you to programmatically access and manipulate the contents and properties of the active cell in a Microsoft Excel worksheet. We can perform many actions depending upon our requirements.
Step 1 – Write VBA Code to use active cell properties
– First of all, we have to activate a suitable cell whose properties we wish to manipulate. There are two ways to achieve this task. First is the manual selection of the cell to make it the active cell. Second is to select that cell programmatically by explicitly addressing that cell in the code as shown above.
– Write the following code in the VBA editor, which can be accessed by pressing ALT+F11. In this code a line is highlighted with a red rectangle and currently we are using the Copy property of the active cell to copy its contents. We will keep on changing the properties and see how these can be used for different purposes.
Step 2 – Run VBA Code to copy active cell’s contents
– From the VBA editor, we can press the play button to run the code and see how it works. So, press the play button and notice that first the code activates cell B4 and then the contents are copied and pasted into cell B10 as shown above.
Step 3 – Change VBA Code to clear active cell’s contents
– We can use the ActiveCell.Clear property after selecting the appropriate cell through code to clear the contents of the cell. So, change the code as follows.
Step 4 – Run VBA Code to clear active cell’s contents
– From the VBA editor, we can press the play button to run the code and see how it works. So, press the play button and notice that first the code activates the cell B4 and then the contents are cleared.
Step 5 – Change VBA Code to get active cell’s Row & Column
– We can use the ActiveCell.Row and ActiveCell.Column properties after selecting the appropriate cell through code to get the Row and Column numbers of the cell. This can be pretty useful in some cases when we want to know the location of a cell meeting a specific condition. So, change the code as follows.
Step 6 – Run VBA Code to get active cell’s Row and Column
– From the VBA editor, we can press the play button to run the code and see how it works. So, press the play button and notice that it gets you the Row number and column number of the currently active cells. If you change the active cell and then run the code again the output will be changed based upon the active cell’s address.
So, this is how you can use the most common of the Active Cell’s properties through VBA.
However, the applications of these properties are not limited to only these methods used in this
tutorial and can be customized as per requirements very easily.
