A range can be referenced one of the objects listed below which can be used to manipulate Excel sheet:
An argument can be a name range, range reference, cell reference etc.
Its default property which is available in Read and Write mode hence without specifying Value property value can be assigned or read from:
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
Range Value is : 15
This property is used to activate the given range and respective range gets focused over.
'Declare range object Dim oRange As Range 'bind selection Set oRange = Selection 'Activate range oRange.Activate
Its method which sets focus on given cell and make selection over as given in below code:
'Declare range object Dim oRange As Range 'bind selection Set oRange = Selection 'Select range oRange.Select
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.
'Declare range object Dim oRange As Range 'bind selection Set oRange = Selection 'Write formula in range oRange.Formula = "=COUNTA(A1:A5)"
A range object offers Cells object which takes two parameters as shown below:
'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.