22.5. Working with the Active Cell or Selection

In a procedure that manipulates a selection that the user has made, you'll typically work with either the active cell or the selection. The active cell is always a single cell, but the selection can encompass multiple cells or other objects.

22.5.1. Working with the Active Cell

The ActiveCell property of the Application object or the Window object returns a Range object that represents the active cell in the Excel application or in the specified window. If you use ActiveCell without specifying the window, VBA returns the active cell in the active window.

For example, the following statement returns the address of the active cell in the active Workbook:

ActiveCell.Address

The following statement returns the text in the active cell in the first window open on the Workbook named Planning.xlsx:

MsgBox Workbooks("Planning.xlsx").Windows(1).ActiveCell.Text

If no worksheet is active, or if a chart sheet is active, there is no active cell. If you try to access ActiveCell, VBA returns an error. So before using code that assumes there is an active cell, check that ActiveCell is not Nothing:

If ActiveCell Is Nothing Then End

22.5.1.1. Getting and Setting the Value of the Active Cell

To return the value of the active cell, use the Value property. For example, the following statement sets the value of the active cell to 25:

ActiveCell.Value = 25

22.5.1.2. Moving the Active Cell to Another Address

The active cell is often convenient to work with, so sometimes you'll want to make a different cell the active cell in order to work with it via the ActiveCell object. To make a cell the active cell, use the Activate method with the appropriate Range object. For example, the following statement makes cell L7 the active cell in the worksheet identified by the object variable myWorksheet:

myWorksheet.Range("B5").Activate

Often, you'll need to move the active cell to a different range a specified number of rows or columns away (in other words, to an address relative to the active cell). To do so, use the Offset property of the active cell object, specifying the number of rows with the RowOffset argument and the number of columns with the ColumnOffset argument. Use a positive offset to move the active cell right or down and a negative offset to move the active cell left or up. For example, the following statement moves the active cell up two rows (RowOffset:=-2) and four columns to the right (ColumnOffset:=4):

ActiveCell.Offset(RowOffset:=-2, ColumnOffset:=4).Activate

In procedures that the user triggers, it's often a good idea to return the active cell to where it was when the user started the procedure. To do so, you can store the location of the active cell and then return it to the stored location after your procedure is finished with its tasks. For example:

Set myActiveCell = ActiveCell
Set myActiveWorksheet = ActiveSheet
Set myActiveWorkbook = ActiveWorkbook

'take actions here

myActiveWorkbook.Activate
myActiveWorksheet.Activate
myActiveCell.Activate

22.5.1.3. Working with the Region around the Active Cell

You can work with the range of cells around the active cell by using the CurrentRegion property to return the CurrentRegion object. The current region extends from the active cell to the first blank row above and below and to the first blank column to the left and right. For example, the following statements use the Font property of the CurrentRegion object to set the font of the current region to 12-point Times New Roman with no bold or italics:

With ActiveCell.CurrentRegion.Font
    .Name = "Times New Roman"
    .Size = 12
    .Bold = False
    .Italic = False
End With

22.5.2. Working with the User's Selection

In macros designed to be run by a user, you will often need to work with cells that the user has selected. For example, a user might select a range of cells and then run a macro to manipulate the contents of the range.

To work with the range the user has selected, use the RangeSelection property of the appropriate Window object. For example, you might assign the RangeSelection property to a range so that you could work with it in a macro and then select in again at the end of the macro, leaving the user ready to work with the selection again.

Dim myMacroRange As Range
Set myMacroRange = ActiveWindow.RangeSelection
With myMacroRange
    'take actions on the range here
End With
myMacroRange.Activate