Excel VBA Range Object

The Range Object in Excel VBA represents a single cell or multiple cells in excel worksheet and it is most important object of Excel VBA.

We can refer a single cell, multiple cells, a rows or a columns, a selection of cells. In this blog, we will give an overview of the properties and methods of Range Objects. Cells are the most important part of Excel and VBA and almost everything we do in Excel VBA start and end with cells.

There are several ways to referring a Range objects:-

1. Range property
2.  Cell property
3. Offset property
4. Union and Intersection Method
5. Current region
6. UsedRenge Property

Range Property

First we will study about what is a range property. Range property is cell or collection of cell and it depends totally as we write the VBA code. In the below given example we write the VBA code and execute it and see what happens, It will help us to understand Range property in a better way.

Next Code is

Next Code is

Next Code is

Next Code is

Cell Property

We can say that Excel is a Cells collection objects and there are multiple cells in a worksheet, so collection of cells is more complicated than collection of worksheet because cell comes in two dimensions.

A Cells  are a intersection of Rows and Columns, so in this case we have to mention two range, one of a row and second of a column like: - [ Cells(3,2).select], 3 for Rows i.e. Third Row, and 2 for Columns i.e. 2nd column. See the below example :-

Code No 1. 

We can see in see in the above image in the second row it is asking for the [Row Index] first and [Column Index] second. i.e. Row first and Column second.

Simply the intersection of Row 3 and Column 2, the value will be "3" as given in the coding.


Code No 2.

In the above image we can see that there are a different coding, Cells(1,2), Cells(4,2). First Cells(1,2) represents a cell which is intersection of First row and Second Column and second cells(4,2) represents a cell which intersection of Fourth row and second column i.e. the range will start from "B1:B4" and we can write this coding is "Range("B1:B4").value=3 Both the coding is correct and same.

But we are discussing about the Cell property, so we will choose the first one.

Offset Property

The Offset property provide another means for referring a range. Like a Cell property, Offset property takes two arguments, The first represents the number of Rows to offset and second represents number of Columns to offset.

The syntax for the Offset property is as follows:

Range.Offset(Row number to Offset, Column number to Offset)

Lets consider Range as A1 and Row no =1, Column no. = 3

Range("A1").Offset(Row Offset:=1, Column Offset:=3) or another way

Range("A1").Offset(1,3).value=11

See the image below :-

The quality of Offset property is,in the above coding Range("A1") is the starting point but when it enter the value it will not count "A1" it just leave that cell or range and count from next cell.

Simply in the  above example it will put the value cell "D2", as code explanation is after Range("A1"), first row and third column put the value "11"

Leave First row and First column as it starts from Range("A1") and then count row and column and enter the value.

Union and Intersection Property

Union and Intersection Method are another two method of range object, where Union method generate a range of two or more range. It can be understand by the below given image :-

Union Method

Here in this image we can see that, in the Excel sheet the shaded range is the combination of two ranges, [Range("A1:B3")] and [Range("B2:D4")]

So Union method range is the range of two or more ranges as we see in the above image and it can be the combination of more than two range also.

Intersect method

Intersect Method is just like the same as Union Method but in Union Method the range is  from two or more range but in Intersect Method represents the intersection of two ranges. We can understand this by below given image :-

Here in the above image we can understand that Intersect Method represents the intersection of two ranges. In this example we can see that intersection falls between two cells "B2" and "B3". We use Union and Intersect Method to find Union and Intersect between two or more ranges.

Current Region

Current Region is a range bounded by any combination of blank rows and blank columns.

We can understand the Current Region Property by the following image in a better way :-

In the above image we can see that every cell is connected with a new data containg cell, so when we apply Current Region property it select all the data which is connected by cell to cell but if any row or column is blank then the Current Region property will not include the range or data which is after blank row or column, as seen in the below image :-

Here we can see in the above example in the image, column is blank, the data has been deleted but column D contains some data and when we execute the code or command of Current Region it will select only two column which are connected by data cell to cell.

So when we apply Current Region Method to select  the range it will select only those cell which are connected with each other the Row and Column should not be blank otherwise the code will not consider the rest part of the data.

UsedRange Property

UsedRange Property is a worksheet property, it returns the are Range bounded by first used cell and last used cell.  "Used Cell" is defined as Cell contains formula, formatting, value that has ever been used or if the value now has been deleted also.

We will understand this by the below given image:-

In the above image we can see that there is no value after cell "A1" but the code is selecting all the cells up to "D3" because in the previous example cell "D3" has a value and deleted by later. 

So this is the quality of UsedRange property that it covers all the area from first cell to last cell contains data or formula or format, whether it has been deleted by later or still contains some data.

So in this tutorial we discuss the Excel VBA  Range Property Objects.

Please suggest for the improvement of this blog, your suggestion are welcome.

Regards

narendrasexceltips.blogspot.com