Dear readers,

Before we discuss the **Range Object** technically, let us see ** what is RANGE in excel?** In a very simple manner if I define Range, I can say that

*This collection can have one or more than one cell.*

**RANGE**is a collections of cells which are in a sequence.
As I have defined, Range is a collection of cells, but why in a sequence? Suppose I have a combination of cells **A1, B2, D7 and E9**. Will I call this combination as a Range? No, I can not call this collection as a Range because these cells are not in a sequence.

If I have a collection of Cells ** A1, B1, C1 and D1** then I can say that this is a Range from **A1** to **D1**. Sequence of cells can be **Vertical** or **Horizontal** or both at the same time which will represent a range in a tabular format.

Now you know what is Range in Excel Workbook. To represent or access this cell range, the Object which is used in Excel VBA is called **Range Object.** Range Object is the most important and used Object in excel VBA. This article will give you an insight about some important and useful properties and methods of the Range Object.

As I said above, in this article you are going to learn some useful properties and methods of Range Object. Before you go there, you should know what is *Property* and Method of an Object in Excel VBA?

# What is Property of an Object?

Property of an Object is something which has an Object. Basically it describes the Object.

# What is Method of an Object?

Method of an Object is something which is performed on that Object.

This is the main difference between a property and method of an Object. Method is an action which is performed on an Object and Property is something which defines the object.

## Syntax

Typically following is the syntax of referring a Range in Excel VBA:

Range(“A1:G5”)

Above statement will represent all the cells from A1 to G5 as shown in below pic:

With above example you can see that

Range(“A1:G5”)is representing the whole area from Cell A1 to G5. This range isUNIQUEin only one WorkSheet because the same range can be found in other worksheet of the same WorkBook. Therefore it becomes necessary to instruct your VBA code to accessRange(“A1:G5”)ofWHICH SHEET?By default it assumes the range from the“Active Sheet”. So, if you want your code to access correct range of correct sheet then you should follow either of the below statements:`Worksheets("SheetName").Range("A1:G5").value '***************OR***************** Worksheets("SheetName").Activate ActiveSheet.Range("A1:G5").value`

## Examples:

Let see in detail by referring to different examples.

## How to read values from a Range Object

As you know a Range can hold one or more than one cell. When a Range is holding a single cell like

Range(“A1”)then it will have a single value which can be stored in a variable easily like below:`Val = Range("A1").Value`

But if your Range has more than one cell then it is stored in an Array format and

Range(“A1:G5”).Valuewill return a Two dimensional array holding all the cells values of that range. To know more about reading such range and dealing with the Arrayread this article.## How to write values in to a Range in Excel VBA

Let us see writing values in a Range using Range Object in VBA.

## i) Range with a Single Cell in Excel VBA

`Range("A1").Value = Val`

Above statement will write the value of variable

in Cell A1. This is simple and no issue with that.Val## i) Range with a Multiple Cells in Excel VBA

`Range("A1:D1").Value = Val`

Now in this case variable

has a single value whereas Range(“A1:D1”) has 4 cells. In such case that single value will be written in all the cells of the Range Object.Val## How to Select a Range through Excel VBA

`Range("A1:D1").Select`

Selection of a range is a Method. This method performs a

Selectionoperation of the given range. This does not return anything hence no variable required to capture the returned value.## How to clear Contents of a Range

`Range("A1:D1").ClearContents`

This is another important method of Range Object. It is used to clear all the contents of a Range.

Note:It clears the data or formula written in a cell but it does not clear the formatting done on a cell or Range. To clear formatting done on a Range, you can use..ClearFormats## Excel VBA to get total count of Rows in a Range

.Rows.Countis the property which returns the total number of rows in the Range.`Msgbox Range("A1:D4").Rows.Count`

Above statement will return Rows Count as 4 because above Range(“A1:D4”) has got 4 rows (From Row 1 to Row 4).

## How to get a total count of Columns in a Range

.Columns.Countis the property which returns the total number of Columns in the Range.`Msgbox Range("A1:D4").Columns.Count`

Above statement will return Column Count as 4 because above Range(“A1:D4”) has got 4 Columns (From Column A to Column D).

## Excel VBA to get count of cells in a Range

.Cells.Countis the property which returns the total number of Cells in the Range.`Msgbox Range("A1:D4").Cells.Count`

Above statement will return total cell Count as 16 because above Range(“A1:D4”) has got 4 columns and 4 Rows hence the total number of cells are 4×4=16.

## How to declare a Range Object in Excel VBA

You can define a Range object simply by following this syntax:

Dim

myRangeVariableas RangeYou can easily assign a Range to this variable as shown in below code:

`Sub range_declaration() Dim myRangeObject As Range myRangeObject = Range("A1:D4") End Sub`

Now you can use your Range variable

same as Range(“A1:D4”). It means you can use all the methods and properties of a Range Object can be used directly onmyRangeObjectvariable.myRangeObjectI have tried to cover many Properties and Methods of Range Object but there are lot more properties and methods available on Range Object. In VBA code windows as soon as you press

sign after Range(“A1:D4”) whole list of Methods and Properties will be displayed. Though Names given to every property and methods are self-explanatory still if you have any question or doubt, you can take help in Excel Help file. It is very rich. If you still need my help, ask me by writing your comment here.DOT (.)

Learn Excel Macro CELLS() and RANGE() - Are they same? Are they Excel VBA Object?July 26, 2014 at 5:59 pm[…] refer value of Cell D4, you can use Range(“B4″).Value. To know more about RANGE() you can read my previous article about Range Object. (adsbygoogle = window.adsbygoogle || […]

weFebruary 9, 2015 at 1:44 pmhow can i display the content of the range of cells in a message box? example i have values in A3, A4, A5.

CELLS() and RANGE() - Are they same? Are they Excel VBA Object? - Welcome to LearnExcelMacro.comSeptember 1, 2017 at 5:39 pm[…] To refer value of Cell D4, you can use Range(“B4”).Value. To know more about RANGE() you can read my previous article about Range Object. […]