VBA Selection Cells: How to Use and Avoid Errors

VBA & macros · Updated November 22, 2026

In VBA, Selection.Cells refers to the collection of cells within the currently selected range, allowing you to read or modify each cell individually. For example, Selection.Cells(1, 1) returns the top-left cell of the selection, and Selection.Cells.Count returns the total number of cells in the selection. This property is part of the Range object and is commonly used in macros that operate on whatever the user has highlighted.

What Is the Selection Object in VBA?

In Excel VBA, Selection is a global object that represents the currently selected element in the active workbook—it could be a range of cells, a chart, a shape, or even a worksheet. When you select cells manually or via code, Selection becomes a Range object. The .Cells property of that range returns a Range object that represents all the cells in the selection, not just the visible ones. This is important: Selection.Cells includes every cell in the selection, even if some are hidden or filtered out.

For instance, if you select A1:B3, Selection.Cells contains six cells. You can access them by row and column index: Selection.Cells(1, 1) is A1, Selection.Cells(2, 3) would be C2 (but only if the selection has three columns). The indexing is always relative to the top-left corner of the selection, not the worksheet.

How to Use Selection.Cells in Your Macro

The most common use is to loop through each cell in the selection. Here’s a simple example that sets the value of every cell in the selection to “Hello”:

Sub FillSelection()
    Dim cell As Range
    ' Save a copy of the workbook before running macros
    For Each cell In Selection.Cells
        cell.Value = "Hello"
    Next cell
End Sub

You can also use Selection.Cells.Count to get the total number of cells. To access a specific cell, use Selection.Cells(row, column). The row and column numbers are relative to the selection’s top-left corner. So if you select C5:E7, Selection.Cells(1, 1) refers to C5, and Selection.Cells(3, 3) refers to E7.

Another common pattern is to use Selection.Cells.SpecialCells(xlCellTypeVisible) to work only with visible cells when the selection includes hidden rows or columns. For example:

Sub FillVisibleOnly()
    Dim visibleCells As Range
    Set visibleCells = Selection.Cells.SpecialCells(xlCellTypeVisible)
    visibleCells.Value = "Visible"
End Sub

This is especially useful when you have filtered data and want to avoid overwriting hidden rows.

How to Select Cells in VBA (The Right Way)

While Selection is handy, it’s often better to avoid it and work directly with a Range object. Selecting cells in code is slow and can cause errors if the user changes the selection while the macro runs. Instead, you can define a range directly:

Sub DirectRange()
    Dim rng As Range
    Set rng = Range("A1:B10")
    rng.Value = 100
End Sub

If you need to select a range first (for example, to copy it), use the Select method:

Range("A1:B10").Select
Selection.Copy

But you can copy without selecting: Range("A1:B10").Copy. Selecting is rarely necessary.

Common Problems and Errors with Selection.Cells

One frequent error is “Select method of Range class failed,” which happens when the worksheet is not active or the workbook is protected. For example, if you try to select a cell on a sheet that isn’t active, you get an error. Always activate the worksheet first:

Worksheets("Sheet1").Activate
Range("A1").Select

Another issue is that Selection may not be a Range at all—if a chart is selected, Selection.Cells will throw an error. You can check the type:

If TypeName(Selection) = "Range" Then
    MsgBox Selection.Cells.Count
End If

Also, Selection.Cells does not automatically exclude hidden rows or columns. Many users expect it to, but it doesn’t. To work only with visible cells, use SpecialCells(xlCellTypeVisible) as shown earlier.

Selection.Cells vs. ActiveCell vs. Range

These three are often confused. Selection is the entire selected area, which can be multiple cells. ActiveCell is the single cell that is currently active (the one with the dark border), even if multiple cells are selected. Range is any defined group of cells, whether selected or not.

For example, if you select A1:B2, ActiveCell might be A1, while Selection includes all four cells. Range can refer to any area, like Range("C5") or Range("A1:B2"). In general, use ActiveCell when you need just the current cell, and Selection when you need the whole highlighted block. But for most macros, it’s cleaner to use a direct Range reference.

Performance and Best Practices

Using Selection can slow down your macro because each Select forces Excel to update the user interface. For large ranges, this can be noticeably slow. To speed up your code, you can turn off screen updating:

Application.ScreenUpdating = False
' ... your code ...
Application.ScreenUpdating = True

Also, avoid looping through individual cells when you can operate on the whole range at once. For example, instead of:

For Each cell In Selection.Cells
    cell.Value = cell.Value * 2
Next cell

You can do:

Selection.Value = Evaluate("IF(ROW(Selection),Selection*2)")

But that’s complex. A simpler approach is to use a variant array:

Sub DoubleValues()
    Dim arr As Variant
    arr = Selection.Value
    Dim i As Long, j As Long
    For i = 1 To UBound(arr, 1)
        For j = 1 To UBound(arr, 2)
            If IsNumeric(arr(i, j)) Then arr(i, j) = arr(i, j) * 2
        Next j
    Next i
    Selection.Value = arr
End Sub

This is much faster because it reads and writes the entire range in one operation.

Frequently Asked Questions

How do I get the number of cells in a selection?

Use Selection.Cells.Count. For example, MsgBox Selection.Cells.Count will display the total number of cells in the selected range. Note that this counts all cells, including hidden ones.

Why does my macro fail when I select a whole column?

Selecting an entire column creates a range with over a million rows, and Selection.Cells.Count returns a huge number (e.g., 1,048,576). Looping through all of them will be extremely slow or cause an out-of-memory error. To avoid this, use Selection.SpecialCells(xlCellTypeVisible) or limit the range to used cells with ActiveSheet.UsedRange.

Can I use Selection.Cells to read a specific cell like C5?

Yes, but the indexing is relative. If your selection starts at A1, then Selection.Cells(3, 3) is C3. To get an absolute reference, use Selection.Cells(3, 3).Address to see the actual address, or use Range("C5") directly.

What is the difference between Selection.Cells and Selection.Rows?

Selection.Cells returns a range of all individual cells, while Selection.Rows returns a range of entire rows within the selection. For example, if you select A1:B2, Selection.Rows.Count is 2, but Selection.Cells.Count is 4. Use Rows when you need to work with whole rows.

How do I avoid using Select in my VBA code?

Instead of selecting a range, just reference it directly. For example, replace Range("A1").Select and then Selection.Value = 5 with Range("A1").Value = 5. This is faster and less error-prone. If you need to copy, use Range("A1").Copy without selecting.

Is Selection.Cells available in Excel for Mac?

Yes, Selection.Cells works in Excel for Mac as well. The VBA language is the same, though some keyboard shortcuts differ (e.g., Cmd instead of Ctrl). The property and its behavior are identical.

Safety and Accuracy Tips for VBA Macros

Before running any macro that modifies cells, always save a copy of your workbook. Macros cannot be undone with Ctrl+Z if they change many cells or if you run them on a large selection. Also, be careful when using Selection in a macro that might be run when the user has a different sheet or a chart selected—always check TypeName(Selection) to avoid runtime errors.

If your macro works with sensitive data, test it on a small sample first. And remember that Selection.Cells includes hidden cells, so if you want to skip them, use SpecialCells(xlCellTypeVisible). Finally, always declare your variables with Dim and use Option Explicit at the top of your module to catch typos.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.