Range
A range can be referenced one of the objects listed below which can be used to manipulate Excel sheet:
- Selection
- Cell
- Row
- Column
- Area
- Offset
- Union
Syntax
Range(Arg)
Arg
An argument can be a name range, range reference, cell reference etc.
Value
Its default property which is available in Read and Write mode hence without specifying Value property value can be assigned or read from:
Code example
Public Sub RangeObjectCode() 'Declare range object Dim oRange As Range 'bind selection Set oRange = Selection 'Set or write value oRange.Value = 15 'Read value Debug.Print "Range Value is : "; oRange.Value 'Memory cleanup Set oRange = Nothing End Sub
Output
Range Value is : 15
Activate
This property is used to activate the given range and respective range gets focused over.
Code example
'Declare range object Dim oRange As Range 'bind selection Set oRange = Selection 'Activate range oRange.Activate
Select
Its method which sets focus on given cell and make selection over as given in below code:
Code example
'Declare range object Dim oRange As Range 'bind selection Set oRange = Selection 'Select range oRange.Select
Formula
Is a property which is used to write a formula in given range. This property requires formula in string format which later gets evaluated by excel later code execution.
Code example
'Declare range object Dim oRange As Range 'bind selection Set oRange = Selection 'Write formula in range oRange.Formula = "=COUNTA(A1:A5)"
Cells object
A range object offers Cells object which takes two parameters as shown below:
Syntax
Cells([RowIndex], [ColumnIndex])
Code example
'Declare range object Dim oRange As Range 'bind selection Set oRange = Selection 'Iterate each cell Dim oCell As Range For Each oCell In oRange.Cells Debug.Print oCell.Value Next oCell
There are many more objects, methods and properties offered by range object as listed below:
- Borders: to decorate range
- Item: comes from Rows collection to obtain specific row
- Delete: Delete row, column, cells or range
- Rows: a collection of rows in specified range
- Columns: a collection of columns in specified range
- Offset: takes two arguments row and column to shift the row and column and return a range
- Count: returns number of rows, columns, cells in the specified object
Please leave your comments or queries under comment section also please do subscribe to our blogs to keep your self upto date.