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.
Important Points
- When you use the active cell property VBA refers to the active cell of the active workbook’s active sheet’s, irrespective of how many workbooks are open at the moment.
- ActiveCell is ultimately a cell that 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.
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, clears 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.
MsgBox ActiveCell.Value
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 in 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 the 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.
MsgBox ActiveCell.Address
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 the Offset
With offset property, you can move to a cell that is several rows and columns away from the active cell.
ActiveCell.Offset(2, 2).Select
Select a Range from the Active Cell
And you can also select a range starting from the active cell.
Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(5, 5)).Select
Related Tutorials
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using VBA
- Special Cells Method in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells