Excel VBA Range Object

What is VBA Range Object?

What is VBA Range Object?  Range object in Excel refers a single cell, multiple cells,  rows or columns, selection of cells. In this blog, we will give an overview of the properties and methods of Range Objects. Cells are the most important object of Excel VBA as a cell is the only creator of a range. 

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 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.

Code No.

Next Code is
Next Code is

Cell Property

Cells are an 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 the Cell property, so we will choose the first one.

Offset Property

The Offset property provides 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 a number of Columns to offset.
The syntax for the Offset property is as follows:
Range.Offset(Row number to Offset, Column number to Offset)
Let’s 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 enters the value it will not count “A1” it just leaves that cell or range and counts 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 counts row and column and enter the value.

Union and Intersection Property

Union and Intersection Method are another two methods of range object, where Union method generate a range of two or more range. It can be understood 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 to a new data containing 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, the 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 is connected by data cell to cell.
So when we apply Current Region Method to select the range it will select only those cell which is connected with each other the Row and Column should not be blank otherwise the code will not consider the best 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 the first cell to the last cell contains data or formula or format, whether it has been deleted by later or still contains some data.
Please suggest for the improvement of this blog, your suggestion is welcome.
Regards
Narendra

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.