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