What are the examples of range in Excel?
Skip to main content This browser is no longer supported. Show Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Range object (Excel)
In this articleRepresents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3D range. Note Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML. RemarksThe default member of Range forwards calls without parameters to the Value property and calls with parameters to
the Item member. Accordingly, The following properties and methods for returning a Range object are described in the Example section:
ExampleUse Range (arg), where arg names the range, to return a Range object that represents a single cell or a range of cells. The following example places the value of cell A1 in cell A5.
The following example fills the range A1:H8 with random numbers by setting the formula for each cell in the range. When it's used without an object qualifier (an object to the left of the period), the Range property returns a range on the active sheet. If the active sheet isn't a worksheet, the method fails. Use the Activate method of the Worksheet object to activate a worksheet before you use the Range property without an explicit object qualifier.
The following example clears the contents of the range named Criteria. Note If you use a text argument for the range address, you must specify the address in A1-style notation (you cannot use R1C1-style notation).
Use Cells on a worksheet to obtain a range consisting all single cells on the worksheet. You can access single cells via Item(row, column), where row is the row index and column is the column index. Item can be omitted since the call is forwarded to it by the default member of Range. The following example sets the value of cell A1 to 24 and of cell B1 to 42 on the first sheet of the active workbook.
The following example sets the formula for cell A2.
Although you can also use Note Although you could
use Visual Basic string functions to alter A1-style references, it is easier (and better programming practice) to use the
Use_expression_.Cells, where expression is an expression that returns a Range object, to obtain a range with the same address consisting of single cells. On such a range, you access single cells via Item(row, column), where are relative to the upper-left corner of the first area of the range. Item can be omitted since the call is forwarded to it by the default member of Range. The following example sets the formula for cell C5 and D5 of the first sheet of the active workbook.
Use Range (cell1, cell2), where cell1 and cell2 are Range objects that specify the start and end cells, to return a Range object. The following example sets the border line style for cells A1:J10. Note Be aware that the period in front of each occurrence of the Cells property is required if the result of the preceding With statement is to be applied to the Cells property. In this case, it indicates that the cells are on worksheet one (without the period, the Cells property would return cells on the active sheet).
Use Rows on a worksheet to obtain a range consisting all rows on the worksheet. You can access single rows via Item(row), where row is the row index. Item can be omitted since the call is forwarded to it by the default member of Range. Note It's not legal to provide the second parameter of Item for ranges consisting of rows. You first have to convert it to single cells via Cells. The following example deletes row 5 and 10 of the first sheet of the active workbook.
Use Columns on a worksheet to obtain a range consisting all columns on the worksheet. You can access single columns via Item(row) [sic], where row is the column index given as a number or as an A1-style column address. Item can be omitted since the call is forwarded to it by the default member of Range. Note It's not legal to provide the second parameter of Item for ranges consisting of columns. You first have to convert it to single cells via Cells. The following example deletes column "B", "C", "E", and "J" of the first sheet of the active workbook.
Use_expression_.Rows, where expression is an expression that returns a Range object, to obtain a range consisting of the rows in the first area of the range. You can access single rows via Item(row), where row is the relative row index from the top of the first area of the range. Item can be omitted since the call is forwarded to it by the default member of Range. Note It's not legal to provide the second parameter of Item for ranges consisting of rows. You first have to convert it to single cells via Cells. The following example deletes the ranges C8:D8 and C6:D6 of the first sheet of the active workbook.
Use_expression_.Columns, where expression is an expression that returns a Range object, to obtain a range consisting of the columns in the first area of the range. You can access single columns via Item(row) [sic], where row is the relative column index from the left of the first area of the range given as a number or as an A1-style column address. Item can be omitted since the call is forwarded to it by the default member of Range. Note It's not legal to provide the second parameter of Item for ranges consisting of columns. You first have to convert it to single cells via Cells. The following example deletes the ranges L2:L10, G2:G10, F2:F10 and D2:D10 of the first sheet of the active workbook.
Use Offset (row, column), where row and column are the row and column offsets, to return a range at a specified offset to another range. The following example selects the cell three rows down from and one column to the right of the cell in the upper-left corner of the current selection. You cannot select a cell that is not on the active sheet, so you must first activate the worksheet.
Use Union (range1, range2, ...) to return multiple-area ranges—that is, ranges composed of two or more contiguous blocks of cells. The following example creates an object defined as the union of ranges A1:B2 and C3:D4, and then selects the defined range.
If you work with selections that contain more than one area, the Areas property is useful. It divides a multiple-area selection into individual Range objects and then returns the objects as a collection. Use the Count property on the returned collection to verify a selection that contains more than one area, as shown in the following example.
This example uses the AdvancedFilter method of the Range object to create a list of the unique values, and the number of times those unique values occur, in the range of column A.
Methods
Properties
See also
Support and feedbackHave questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback. What is range type in Excel?Whenever we see the word range in excel, we refer to it as a cell or a collection of cells in an excel spreadsheet. It can also be used to refer to the adjacent cells or non-adjacent cells in the dataset. In excel, each range has its defined set of coordinates or positions, unlike A4:A7, B5: F9C, etc.
What is the correct example of a cell range?When referring to a spreadsheet, the range or cell range is a group of cells within a row or column. For example, in the formula =sum(A1:A10), the cells in column A1 through A10 are the range of cells that are added together.
What are range names in Excel?Range names are names used to refer to cell references, formula results, or values. They are often used to avoid hard-coded values appearing in formulas and to make formulas clearer in general. They are stored in what is known as the Name Manager in Excel.
|