FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA Active Cell

Updated on June 5, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Active Cell in Excel VBA

The active cell is the currently selected cell in a worksheet. The active cell in VBA can be used as a reference to move to another cell or change the properties of the same active cell or the cell reference provided by the active cell. We can access an active cell in VBA by using the application.property method with the keyword active cell.

Understanding the concept of range object and cell properties in VBA is important to work efficiently with VBA coding. One more concept you need to look into in these concepts is “VBA Active Cell.”

In Excel, there are millions of cells, and you are unsure which one is an active cell. For example, look at the below image.

Active cell address

In the above pic, we have many cells. Finding which one is an active cell is very simple; whichever cell is selected. It is called an “active cell” in VBA.

Look at the name box if your active cell is not visible in your window. It will show you the active cell address. For example, in the above image, the active cell address is B3.

Even when many cells are selected as a range of cells, whatever the first cell is in, the selection becomes the active cell. For example, look at the below image.

Active cell address range

#1 – Referencing in Excel VBA

In our earlier articles, we have seen how to reference the cells in VBA. By active cell property, we can refer to the cell.

For example, if we want to select cell A1 and insert the value “Hello,” we can write it in two ways. Below is the way of selecting the cell and inserting the value using the VBA “RANGE” object

Code:

Sub ActiveCell_Example1()

    Range("A1").Select
    Range("A1").Value = "Hello"
 
End Sub
VBA Active Cell Example 1

It will first select the cell A1 “Range(“A1″). Select”

Then, it will insert the value “Hello” in cell A1 Range(“A1”).Value = “Hello”

Now, we will remove the line Range(“A1”). Value = “Hello” and use the active cell property to insert the value.

Code:

Sub ActiveCell_Example1()

    Range("A1").Select
    ActiveCell.Value = "Hello"

End Sub
VBA Active Cell Example 1-1

Similarly, first, it will select the cell A1 “Range(“A1”). Select.

But here, we have used ActiveCell.Value = “Hello” instead of Range(“A1”).Value = “Hello”

We have used the active cell property because the moment we select cell A1 it becomes an active cell. So, we can use the Excel VBA active cell property to insert the value.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

#2 – Active Cell Address, Value, Row, and Column Number

Let’s show the active cell’s address in the message box to understand it better. Now, look at the below image.

VBA Active Cell Example 2

In the above image, the active cell is “B3,” and the value is 55. So, let us write code in VBA to get the active cell’s address.

Code:

Sub ActiveCell_Example2()

    MsgBox ActiveCell.Address

End Sub
VBA Active Cell Example 2-1

Run this code using the F5 key or manually. Then, it will show the active cell’s address in a message box.

VBA Active Cell Example 2-2

Output:

VBA Active Cell Example 2-3

Similarly, the below code will show the value of the active cell.

Code:

Sub ActiveCell_Example2()

    MsgBox ActiveCell.Value

End Sub
VBA Active Cell Example 2-4

Output:

VBA Active Cell Example 2-5

The below code will show the row number of the active cell.

Code:

Sub ActiveCell_Example2()

  MsgBox ActiveCell.Row

End Sub
VBA Active Cell Example 2-6

Output:

Example 2-7

The below code will show the column number of the active cell.

Code:

Sub ActiveCell_Example2()

MsgBox ActiveCell.Column

End Sub
Example 2-8

Output:

VBA Active Cell Example 2-9

#3 – Parameters of Active Cell in Excel VBA

The active cell property has parameters as well. After entering the property, the active cell opens parenthesis to see the parameters.

VBA Active Cell Formula

Using this parameter, we can refer to another cell as well.

For example, ActiveCell (1,1) means whichever cell is active. If you want to move down one row to the bottom, you can use ActiveCell (2,1). Here 2 does not mean moving down two rows but rather just one row down. Similarly, if you want to move one column to the right, then this is the code ActiveCell (2,2)

Look at the below image.

VBA Active Cell Example 3

In the above image, the active cell is A2. To insert value to the active cell, you write this code.

Code:

ActiveCell.Value = “Hiiii” or ActiveCell (1,1).Value = “Hiiii”
VBA Active Cell Example 3-4

Run this code manually or through the F5 key. It will insert the value “Hiiii” into the cell.

Example 3-7

If you want to insert the same value to the below cell, you can use this code.

Code:

ActiveCell (2,1).Value = “Hiiii”
Example 3-5

It will insert the value to the cell below the active cell.

Example 3-2

You can use this code if you want to insert the value to one column right then.

Code:

ActiveCell (1,2).Value = “Hiiii”
Example 3-6

It will insert “Hiiii” to the next column cell of the active cell.

Example 3-3

Like this, we can reference the cells in VBA using the active cell property.

We hope you have enjoyed it. Thanks for your time with us.

You can download the VBA Active Cell Excel Template here:- VBA Active Cell Template

This article has been a guide to VBA Active Cell. Here, we learned the concept of an active cell to find the address of a cell. Also, we learned the parameters of the active cell in Excel VBA along with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –