In VBA, the active cell is a property that represents the cell that is active at the moment. When you select a cell or navigate to a cell and that green box covers that cell you can use ACTIVECELL property to refer to that cell in a VBA code. There are properties and methods that come with it.
Use the Active Cell Property
- Type the keyword “ActiveCell”.
- Type a dot (.) to get the list properties and methods .
- Select the property or method that you want to use.
- Run the code to perform the activity to the active cell.
- When you use the active cell property VBA refers to the active cell of the active workbook’s active sheet’s active cell, irrespective of how many workbooks are open at the moment.
- ActiveCell is a cell ultimately and it comes with all the properties and methods that a normal cell comes with.
Activate a Cell from the Selected Range
To activate a cell using a VBA code there are two ways that you can use one “Activate” method and “Select” method (link).
Sub vba_activecell() 'select and entire range Range("A1:A10").Select 'select the cell A3 from the selected range Range("A3").Activate 'clears everything from the active cell ActiveCell.Clear End Sub
The above code, first of all, selects the range A1:A10 and then activates the cell A3 out of that and in the end, clear everything from the active cell i.e., A3.
Return Value from the Active Cell
The following code returns the value from the active cell using a message box.
Or if you want to get the value from the active cell and paste it into a separate cell.
Range("A1") = ActiveCell.Value
Set Active Cell to a Variable
You can also set the active cell to the variable, just like the following example.
Sub vba_activecell() 'declares the variable as range Dim myCell As Range 'set active cell to the variable Set myCell = ActiveCell 'enter value in the active cell myCell.Value = Done End Sub
Get Row and Column Number of the ActiveCell
With the active cell there comes a row and column property that you can use to get the row and column number of the active cell.
MsgBox ActiveCell.Row MsgBox ActiveCell.Column
Get Active Cell’s Address
You can use the address property to get the address of the active cell.
When you run the above code, it shows you a message box with the cell address of the active cell of the active workbook’s active sheet (as I mentioned earlier).
Move from the Active Cell using Offset
With offset property you can move to a cell which is a number of rows and columns aways from the active cell.
Select a Range from the Active Cell
And you can also select a range from the starting from the active cell.
Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(5, 5)).Select