ActiveCell vs Selection VBA for Excel

Add to Favorites
Author:

The difference between ActiveCell and Selection in Excel VBA and when to use them.

Both properties allow you to work with cells in a worksheet, but they operate in slightly different ways.

Sections:

The Difference

ActiveCell Examples

Selection Examples

Notes

The Difference

ActiveCell works only with a single cell, the currently active cell, which is the cell that text would be input into if you started typing while viewing the worksheet; this can be a single cell or the currently active cell in a range of selected cells - this can seem confusing so the examples below will illustrate this.

Selection works on every single cell that is selected by the user; this could be one cell or many cells at the same time.

ActiveCell Examples

Sub ActiveCell_Example()

ActiveCell.Value = "Hi!"

End Sub

This will input the value Hi! into the active cell.

Example 1 - Single Cell

User selects a single cell and runs the above macro:

1f028efe07581f65c51198d174b7d9f1.png

Example 2 - Multiple Cells/Range

User selects multiple cells and runs the macro:

76f94d0e976567afebd1bab478f972c8.png

Notice that in this example only one cell had a value input into it!

The cell that got the value is the cell that would have a value when you start typing from the keyboard.

The main thing to remember is that ActiveCell only works on a single cell.

Example 3 - Multiple Non-Contiguous Cells

User selects multiple cells that are not adjacent to one another.

d13253ea7b0c64b97fbad9b28178e583.png

Once again, only one cell gets a value.

In this case, the cell that is selected last will get the value.

Selection Examples

Sub Selection_Example()

Selection.Value = "Hi!"

End Sub

This macro inputs the value Hi! into every single cell that the user has selected.

Example 1 - Single Cell

User selects a single cell and runs the macro:

bc45dfb3a8dd8b31a021f36eb2dfc99d.png

Example 2 - Multiple Cells/Range

User selects multiple cells in a range:

59661631c8fa271537bd46549910ea7b.png

Every cell that was selected gets the value.

Example 3 - Multiple Non-Contiguous Cells

User selects multiple cells that are not adjacent to one another.

4ba5881999fa3cafe58008c4970adbbf.png

Once again, every cell that was selected gets the value.

It doesn't matter how the cells are selected or where they are, each selected cell is acted upon when you use the Selection property for VBA in macros in Excel

Notes

ActiveCell works on only 1 cell at a time.

Selection works on all cells that have been selected.

These properties allow you to do anything with the cell, not just input a value; for instance, you can use them to change the font, color, formulas, and any other thing related to a cell in Excel.

Download the sample file to work with these examples in Excel.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
Variables in Macros VBA for Excel - Practical Guide
Tutorial: This is a practical guide to using Variables in Macros and VBA for Excel. I will tell you ...
Remove Dashed Lines from Copy Paste VBA in Excel
Tutorial: How to remove the flashing dashes from a copy/paste range using VBA in Excel; this removes...
Activate or Navigate to a Worksheet using Macros VBA in Excel
Tutorial: Make a particular worksheet visible using a macro in Excel. This is called activating a wo...
Get the Last Row using VBA in Excel
Tutorial: How to find the last row of data using a Macro/VBA in Excel, including getting the number ...
Copy and Paste Data using Macro VBA in Excel
Tutorial: How to copy and paste data using a Macro in Excel. I'll show you multiple ways to do this,...
Print Selected Worksheets in Excel
Macro: This free excel macro will print all selected worksheets in Excel. If there are no ot...