Working With Range Object in Excel VBA – Complete Tutorial

Range Object in Excel VBA

In Excel VBA, the Range object is one of the most important tools for working with cells. In Excel, a range is a cell or a collection of cells used for various purposes, including formatting, calculations, and data analysis. In this chapter, you will learn about the main properties and methods of the Range object. Properties describe what an object is, and methods define what tasks it can perform.

Range Examples

Insert a command button on your worksheet, then enter the following code:

Range("B3").Value = 2

Results when you clicked the command button:

Code:

Range("A1:A4").Value = 5

Result:

Code:

Range("A1:A2,B3:C4").Value = 10

Result:

Note: This is the correct way to refer to a named range using VBA in Excel.

Range("Prices").Value = 15

Cells

Instead of Range, you can also use Cells. Using the Cells property is especially helpful when you need to work with ranges in a loop.

Code:

Cells(3, 2).Value = 2

Result:

Explanation: VBA transfers the value 2 to the cell at row 3, column 2.

Code:

Range(Cells(1, 1), Cells(4, 1)).Value = 5

Result:

Declare a Range Object

To create a Range object, declare it using “Dim” and assign it with “Set”.

Code:

Dim example As Range
Set example = Range("A1:C4")

example.Value = 8

Result:

Select

With the Select method, the Range object can choose one or more cells on a worksheet.

Code:

Dim example As Range
Set example = Range("A1:C4")

example.Select

Result:

Note: Before selecting cells on a different worksheet, make sure you activate that worksheet first. This code selects cell B7 on the third worksheet of a workbook, counting worksheets from the left-hand side.

Worksheets(3).Activate
Worksheets(3).Range("B7").Select

Rows

The Rows property in Excel allows you to select a specific row within a range, making row-based operations easier. It helps you perform actions on that specific row.

Code:

Dim example As Range
Set example = Range("A1:C4")

example.Rows(3).Select

Result:

Note: Borders are added in the image for explanation only.

Columns

The Columns property in Excel VBA helps you access, select, and manage a specific column within a range of cells.

Code:

Dim example As Range
Set example = Range("A1:C4")

example.Columns(2).Select

Result:

Note: Borders are added in the image for explanation only.

Copy/Paste

You can use the Copy method to copy a range of cells, and then use the Paste method to insert it somewhere else in the worksheet.

Code:

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

Range("C3").Select
ActiveSheet.Paste

Result:

This is allowed in Excel VBA, but the line below is a better way to do the same task.

Range("C3:C4").Value = Range("A1:A2").Value

Clear

To remove everything inside a range but leave the range itself, you can use the ClearContents method in Excel.

Range("A1").ClearContents

or simply use:

Range("A1").Value = ""

Note: The Clear method will erase everything from a range, including its content and formatting. Use the ClearFormats method to erase the format without affecting the data.

Count

The Count property helps you identify how many elements, such as cells, rows, or columns, exist in a selected range.

Note: Borders are added in the image for explanation only.

Code:

Dim example As Range
Set example = Range("A1:C4")

MsgBox example.Count

Result:

Code:

Dim example As Range
Set example = Range("A1:C4")

MsgBox example.Rows.Count

Result:

Note: in a similar way, you can also count how many columns a particular range contains.

1/14 Completed! Discover details about the Range object.➝
Next Chapter: Variables