Active Cell in Excel VBA

Understanding the concept of range object and cell properties in VBA Properties In VBACells are cells of the worksheet, and in VBA, when we refer to cells as a range property, we refer to the same cells. In VBA concepts, cells are also the same, no different from normal excel cells.read more is important to work efficiently with VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. 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 boxName BoxIn Excel, the name box is located on the left side of the window and is used to give a name to a table or a cell. The name is usually the row character followed by the column number, such as cell A1.read more 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.

#1 – Referencing in Excel VBA

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.

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.

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.

Sub ActiveCell_Example2()

MsgBox ActiveCell.Value

End Sub

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

Sub ActiveCell_Example2()

MsgBox ActiveCell.Row

End Sub

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

Sub ActiveCell_Example2()

MsgBox ActiveCell.Column

End Sub

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

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.

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.

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

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: –

  • VBA SelectionExcel Edit Cell ShortcutExcel VBA Range CellsGet Cell Value with Excel VBA