VBA Active Cell

Last Updated :

21 Aug, 2024

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

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.

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 in Excel VBA

#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

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

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.

#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.

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

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

Output:

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

Code:

Sub ActiveCell_Example2()

    MsgBox ActiveCell.Value

End Sub

Output:

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

Code:

Sub ActiveCell_Example2()

  MsgBox ActiveCell.Row

End Sub

Output:

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

Code:

Sub ActiveCell_Example2()

MsgBox ActiveCell.Column

End Sub

Output:

#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.

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.

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”

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

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

Code:

ActiveCell (2,1).Value = “Hiiii”

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

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

Code:

ActiveCell (1,2).Value = “Hiiii”

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

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

VBA Active Cell

Active Cell in Excel VBA

Table of contents

#1 - Referencing in Excel VBA

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

#3 - Parameters of Active Cell in Excel VBA

Recommended Articles