Excel - ActiveCell

Card Puncher Data Processing

Excel - ActiveCell

About

The ActiveCell property of a worksheet returns a Range object that represents the cell that is active. You can apply any of the properties or methods of a Range object to the active cell

Be careful to distinguish between the active cell and the selection. The active cell is a single cell inside the current selection. The selection may contain more than one cell, but only one is the active cell.

Management

Initialization

You can use the Activate method to designate which cell is the active cell.

Sub SetActive() 
 Worksheets("Sheet1").Activate 
 'B5 is the active cel
 Worksheets("Sheet1").Range("B5").Activate 
 ActiveCell.Font.Bold = True 
End Sub

Qualification

If you don't specify an object qualifier, this property returns the active cell in the active window.

You can work with the active cell only when the worksheet that it is on is the active sheet.

The following expressions all return the active cell, and are all equivalent.

ActiveCell
Application.ActiveCell
ActiveWindow.ActiveCell
Application.ActiveWindow.ActiveCell

changes the font formatting for the active cell with the with statement

'You can work with the active cell only when the worksheet that it is on is the active sheet.
Worksheets("Sheet1").Activate
With ActiveCell.Font
    .Bold = True
    .Italic = True
End With
ActiveCell.Value = 35 

Move

You can use the Offset property to move the active cell.

The following procedure inserts text into the active cell in the selected range and then moves the active cell one cell to the right without changing the selection.

Sub MoveActive() 
 Worksheets("Sheet1").Activate 
 Range("A1:D10").Select 
 ActiveCell.Value = "Monthly Totals" 
 ActiveCell.Offset(0, 1).Activate 
End Sub

Methods

CurrentRegion

The CurrentRegion property returns a range of cells bounded by blank rows and columns.

If you do not know the boundaries of the range, the CurrentRegion property will return the range that surrounds the active cell (ie the cell selection will stop when an empty row and an empty column is found around the active cell)

You can then also loop through it.

Sub RoundToZero3() 
 Worksheets("Sheet1").Activate
 For Each c In ActiveCell.CurrentRegion.Cells 
   If Abs(c.Value) < 0.01 Then c.Value = 0 
 Next 
End Sub





Discover More
Card Puncher Data Processing
Excel - Cell

In Microsoft Excel, you usually select a cell (see active cell) or cells and then perform an action, such as formatting the cells or entering values in them. In Visual Basic, it is usually not necessary...



Share this page:
Follow us:
Task Runner