How to Use Active Cell in VBA

In one of the previous articles, we learned about cells and ranges. Today, we will learn to use Active Cell in VBA. We will learn to set the value of the Active Cell, get value from Active Cell, get row and column numbers corresponding to the active cell, and much more.

So let us start learning.

What is ActiveCell Property in VBA?

Active Cell refers to the cell in edit mode if you start to type.

Here B2 is the active cell. In VBA, we have this ActiveCell property which represents the cell that is currently active at the moment. We can use this property to refer to the Active Cell in our VBA code. It has its own properties and methods associated with it.

For instance, this Sub Procedure when executed would change the value of the active cell in the currently active worksheet of the opened workbook to ExcelUnlocked.

Sub exChangeActiveCellValue()
'change the value of active cell
ActiveCell.Value = "ExcelUnlocked"
End Sub

Syntax to use ActiveCell

ActiveCell.<property>

When we use the ActiveCell property, it automatically refers to the active cell in the active workbook. It does not matter how many workbooks were opened at that moment.

However, if you wish to access the contents of Active Cell in a specific worksheet of a workbook then we can use the collections.

Examples to Use ActiveCell Property

In this section of the article, we would implement some examples to learn to use ActiveCell Property.

Example 1 – Activate a Cell from Selected Range of Cells and Clear the Contents

We need to select a range using the Select Property of Range and then Activate a cell within that range using the Activate Property.

Enter the following code in a new module.

Sub exActivateCell()

'select the range A1:D4
Range("A1:D4").Select

'activate the cell B3
Range("B3").Activate

'clear the contents in active cell
ActiveCell.Clear

End Sub

When you run this Sub Procedure, it will select the range A1:D4 of Active Worksheet, the active cell would be B3 and then its contents will be cleared.

Note that this also clears the formatting from the active cell.

Example 2 – Get the Value of Active Cell

In this example, we would access the value of the active cell using the Value property and then print the contents in Active Cell using the Message Box.

Sub getActiveCellValue()
'declare a variant to store value in active cell
Dim value
'store the value of active cell in variant
value = ActiveCell.value
'print the variant
MsgBox value
End Sub

Run this sub procedure, it will print the value of active cell in a message box on the screen.

Example 3 – Set the Value of Active Cell to a Variable

We will set the value of the active cell to a range variable and then change the value of the variable to update the value in the active cell of a worksheet.

Sub exSetValueToVariable()

'declare a range object
Dim myvariable As Range

'set the value of range variable to active cell
Set myvariable = ActiveCell

'enter the value in range
myvariable.value = "Hey!"

'change the interior color of range
myvariable.Interior.Color = RGB(255,204,229)

End Sub

When you run this Sub Procedure, it enters a text string “Hey!” to the active cell and also changes its interior color.

Example 4 – Active Cell with Offset

Using the Offset Property, we can refer to a cell that is a fixed number of rows and columns to the active cell.

ActiveCell.Offset(<row>,<column>).[property]

For example, this would select the cell one row below and three columns to the right of the active cell.

This brings us to an end.

Thank you for reading.