ActiveCell In Excel VBA - When To Use And When It's Downright ...

ThisWorkbook.Activate Worksheets("Sheet1").Range("B4").Select

And now when you run MsgBox selectedCell.Address you will get the output as “$B$4”

Example 4: Select last row in the active column using ActiveCell

Consider the below snapshot. The ActiveCell is in the first column.

If you want to select the last cell that has data in this active column, you can use the code snippet below.

ThisWorkbook.Activate Application.activecell.End(XlDirection.xlDown).Select

After the execution of the code, the last cell will be selected like this

Example 5: Selecting the cells that contain data around the ActiveCell using CurrentRegion

The CurrentRegion property returns a range of cells bounded by blank rows and columns. In the following example, the selection is expanded to include the cells adjoining the active cell that contain data and the background color of the current region is modified. The entire range is copied to another sheet at the same location.

Sub CurrRegion() Dim curReg As Range ThisWorkbook.Activate 'Get the current region and assign it to a Range variable Set curReg = ActiveCell.CurrentRegion ActiveCell.CurrentRegion.Select 'Format it Selection.Interior.Color = vbCyan 'Select the first cell in the range Application.ActiveCell.End(XlDirection.xlUp).Select Application.ActiveCell.End(XlDirection.xlToLeft).Select 'Paste the current region in another sheet at the same location curReg.Copy Worksheets("Sheet2").Range(Application.ActiveCell.Address) End Sub

The current region can be useful when the range of data is not fixed and you need to perform certain operations on it, like format it, copy it, insert charts or send an email using that range.

Example 6: Using offset with ActiveCell

Now let us look at a more practical example of using ActiveCell. Say, in your Excel sheet you have multiple lines of data as shown below and you need to process data only for a single selected row.

The desired output is that in the column “Gaining”, Y or N should be inputted based on the price level and the background color should be set to Green or Red respectively.

Here, we will assume that the stock name is selected before running the macro. To get the values of the Previous Close and Current Price we will use the Offset Method

Sub offset() Dim preClose As Double, currPrice As Double ThisWorkbook.Activate 'Get value from the second column i.e. offset of zero rows and one column preClose = ActiveCell.offset(0, 1).Value 'Get value from the third column i.e. offset of zero rows and two columns currPrice = ActiveCell.offset(0, 2).Value If currPrice < preClose Then 'Set value and format of the forth column i.e. offset of zero rows and three columns ActiveCell.offset(0, 3).Value = "N" ActiveCell.offset(0, 3).Interior.Color = vbRed Else ActiveCell.offset(0, 3).Value = "Y" ActiveCell.offset(0, 3).Interior.Color = vbGreen End If End Sub

For further details on the Offset Method, please refer to the article “How to use VBA Range.Offset Method”

You can easily avoid the use of ActiveCell here, by using the input box to get the stock name / row number from the user.  After running the code on the last row the output will look like this.

See also: “VBA, Excel Automation From Other Applications“