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 RANGE is a collections of cells which are in a sequence. This collection can have one or more than one cell.

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:

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

Excel VBA - Rang Object

With above example you can see that Range(“A1:G5”) is representing the whole area from Cell A1 to G5. This range is UNIQUE in 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 access Range(“A1:G5”) of WHICH 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”).Value will return a Two dimensional array holding all the cells values of that range. To know more about reading such range and dealing with the Array read 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 Val in Cell A1. This is simple and no issue with that.

i) Range with a Multiple Cells in Excel VBA

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

Now in this case variable Val 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.

How to Select a Range through Excel VBA

Range("A1:D1").Select

Selection of a range is a Method. This method performs a Selection operation 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.Count is 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.Count is 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.Count is 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 myRangeVariable as Range

You 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 myRangeObject same as Range(“A1:D4”). It means you can use all the methods and properties of a Range Object can be used directly on myRangeObject variable.

I 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 DOT (.) 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.