Excel VBA: How to Select Single, Multiple, Non-Contiguous, Row, Column, and Active Cells

Contents

Excel VBA: How to Select Single, Multiple, Non-Contiguous, Row, Column, and Active Cells

Selecting cells is one of the most common actions you’ll perform in Excel VBA — yet it’s also one of the most misunderstood.
Whether you need to select a single cell, multiple ranges, entire rows or columns, or dynamically work with the active cell, mastering selection techniques will make your macros more efficient and reliable.

In this comprehensive guide, you’ll learn how to:

Select a single cell or a range programmatically
Select multiple or non-contiguous ranges
Select entire rows or columns
Work with the ActiveCell and Selection object
Understand when and why to avoid .Select for performance
Apply these concepts safely in automation and RPA workflows

By the end, you’ll be able to handle any type of selection — from a simple range to dynamic, multi-area selections — with confidence.

✅ What Does “Selecting a Cell” Mean in VBA?

When you use .Select in VBA, Excel highlights that cell or range visually, just as if a user had clicked on it.
However, not all VBA tasks require selecting — you can often manipulate ranges directly, which is faster.

But understanding how .Select works is essential for:

Interactive macros that guide users
Formatting tasks that need visible confirmation
Debugging scripts
User-driven automation like UiPath or Power Automate

Let’s go through every selection type step by step.

✅ How to Select a Single Cell in VBA

The most basic operation: selecting one specific cell.

・Example 1: Select a Cell by Reference

Range("A1").Select

✅ Highlights cell A1 on the active sheet.

・Example 2: Select a Cell Using Cells Property

Cells(3, 2).Select

✅ Selects the cell at row 3, column 2 — i.e., cell B3.

This method is dynamic and ideal when you need to select based on numeric positions.

・Example 3: Select a Cell in Another Sheet

You must activate the sheet before selecting a cell.

Sheets("Data").Activate
Range("B2").Select

✅ Activates the sheet and selects cell B2.

If you omit Activate, you’ll get an error:

Run-time error ‘1004’: Select method of Range class failed.

・Example 4: Select a Cell Dynamically

Dim rowNum As Long
rowNum = 10
Range("A" & rowNum).Select

✅ Selects cell A10 — useful for dynamic navigation.

✅ How to Select Multiple Cells (Continuous Range)

To select a range of adjacent cells:

・Example 1: Simple Continuous Range

Range("A1:C3").Select

✅ Selects a 3×3 block from A1 to C3.

・Example 2: Using Cells for Dynamic Range

Range(Cells(1, 1), Cells(5, 3)).Select

✅ Selects the range A1:C5 dynamically — great for variable-sized data.

・Example 3: Selecting Range with Variables

Dim startRow As Long, endRow As Long
startRow = 2
endRow = 10
Range("A" & startRow & ":A" & endRow).Select

✅ Selects column A from row 2 to 10.

✅ How to Select Non-Contiguous Cells or Ranges

Sometimes you need to select multiple separate areas (for example, “A1:A3” and “C1:C3”).

・Example 1: Multiple Area Selection

Range("A1:A3, C1:C3").Select

✅ Selects two non-contiguous ranges simultaneously.

・Example 2: Add Selection Using Union

Union(Range("A1:A3"), Range("C1:C3")).Select

✅ Combines separate ranges into a single selection programmatically.

・Example 3: Combine Dynamic Areas

Dim rng1 As Range, rng2 As Range
Set rng1 = Range("A1:A5")
Set rng2 = Range("C1:C5")
Union(rng1, rng2).Select

✅ Flexible approach for combining multiple variable ranges.

✅ How to Select Entire Rows

・Example 1: Select One Row

Rows("3:3").Select

✅ Selects the entire row 3.

・Example 2: Select Multiple Rows

Rows("2:5").Select

✅ Selects rows 2 through 5.

・Example 3: Dynamic Row Selection

Dim r As Long
r = ActiveCell.Row
Rows(r).Select

✅ Selects the row of the currently active cell.

・Example 4: Select Non-Adjacent Rows

Union(Rows(2), Rows(4)).Select

✅ Selects row 2 and row 4 at the same time.

✅ How to Select Entire Columns

・Example 1: Select a Single Column

Columns("B:B").Select

✅ Selects all of column B.

・Example 2: Select Multiple Columns

Columns("A:C").Select

✅ Selects columns A through C.

・Example 3: Dynamic Column Selection

Dim colNum As Long
colNum = ActiveCell.Column
Columns(colNum).Select

✅ Selects the column containing the active cell.

・Example 4: Non-Contiguous Column Selection

Union(Columns("A"), Columns("C")).Select

✅ Selects columns A and C together.

✅ Selecting the Active Cell Programmatically

The ActiveCell object represents the currently selected cell.

・Example 1: Display the Active Cell Address

MsgBox ActiveCell.Address

✅ Shows the address of the selected cell (e.g., $B$3).

・Example 2: Change Value in the Active Cell

ActiveCell.Value = "Updated"

✅ Writes data to whichever cell is currently active.

・Example 3: Move from the Active Cell

You can shift the selection relative to the ActiveCell.

ActiveCell.Offset(1, 0).Select

✅ Moves down one row.

ActiveCell.Offset(0, 1).Select

✅ Moves right one column.

✅ How to Select the Last Used Cell in a Sheet

You can find and select the last non-empty cell in the sheet:

Sub SelectLastCell()
Dim lastCell As Range
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
lastCell.Select
End Sub

✅ Jumps to the last used cell.

✅ Selecting Ranges Relative to the Active Cell

VBA allows you to use Offset and Resize to expand selections dynamically.

・Example 1: Select a Block of Cells

ActiveCell.Resize(3, 2).Select

✅ Selects a 3×2 area starting from the active cell.

・Example 2: Select Adjacent Range

Range(ActiveCell, ActiveCell.Offset(2, 2)).Select

✅ Selects from the active cell to two rows down and two columns right.

Excel VBA: What Is ActiveCell — How to Use and Control the Active Cell in Your Macros

✅ Selecting UsedRange or CurrentRegion

・Example 1: Select All Used Cells

ActiveSheet.UsedRange.Select

✅ Selects all cells that contain data.

・Example 2: Select the CurrentRegion Around a Cell

Range("A1").CurrentRegion.Select

✅ Selects the entire block of connected data surrounding A1.

・Example 3: Dynamic CurrentRegion Selection

ActiveCell.CurrentRegion.Select

✅ Automatically selects the table or range surrounding the active cell.

✅ Selecting Visible Cells Only

When filtering data, hidden rows remain part of the selection unless you limit it to visible cells.

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Select

✅ Selects only visible cells — perfect for filtered data.

✅ Selecting Cells by Criteria (Example: Empty Cells)

ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Select

✅ Selects all blank cells in the used range.

✅ Common Selection Errors and How to Fix Them

Error	Cause	Solution
Select method of Range class failed	Sheet not active	Activate sheet first
No cells found	No cells match criteria	Add error handling
Selection flickers	ScreenUpdating enabled	Disable ScreenUpdating
Bot cannot select cell	RPA running invisibly	Use direct references
Wrong sheet selected	Workbook not activated	Activate workbook first

・Example: Safe Selection with Error Handling

On Error Resume Next
Range("A1").SpecialCells(xlCellTypeVisible).Select
If Err.Number <> 0 Then
MsgBox "No visible cells found."
Err.Clear
End If
On Error GoTo 0

✅ How to Avoid .Select for Faster Code

While .Select is useful for visual navigation, it’s not required for most VBA operations.
Instead, directly manipulate ranges:

・Example: Without Select

Range("A1").Value = "Hello"
Range("B1").Font.Bold = True

・Example: With Select (Slower)

Range("A1").Select
Selection.Value = "Hello"
Selection.Font.Bold = True

✅ The first version is faster and cleaner — preferred for automation.

✅ Using Selection in UiPath or Power Automate Automations

When Excel is automated by RPA tools like UiPath, selection and activation behave differently because Excel may not have a visible interface.

Recommendations:

Use direct references like Workbook.Sheets("Sheet1").Range("A1") instead of .Select.
When debugging, temporarily enable Excel’s visibility: Application.Visible = True
Avoid relying on ActiveCell during background automation.

✅ These practices ensure reliable automation in both attended and unattended bots.

✅ Performance Tips for Working with Selections

Tip	Description
Disable `ScreenUpdating`	Prevent flicker
Avoid `.Select` in loops	Improves speed drastically
Use `Union` for multiple areas	Cleaner multi-range selection
Check `Visible` before activating	Avoid runtime errors
Store selections in variables	Prevent unexpected context changes

Example:

Application.ScreenUpdating = False
Range("A1:B10").Select
' Do work here
Application.ScreenUpdating = True

✅ Summary: Master Cell Selection in Excel VBA

Use Range("A1") or Cells(row, col) to select single cells.
Combine multiple ranges with Union.
Select entire rows with Rows(), columns with Columns().
Use ActiveCell, CurrentRegion, and UsedRange for dynamic selection.
Always activate the sheet before selecting cells on it.
Avoid unnecessary .Select for better performance — use direct references.
For automation (UiPath, Power Automate), rely on object references instead of visible selection.
Combine selection logic with error handling and ScreenUpdating control for smoother execution.

By mastering single, multiple, and non-contiguous cell selection, you’ll gain precise control over your Excel VBA automations — from user-guided macros to high-speed RPA workflows.

VBA Cell & Sheet Operations: The Complete Guide to Manipulating Cells, Ranges, and Worksheets