Range Power Excel Add-in with VBA code example

Range

A range can be referenced one of the objects listed below which can be used to manipulate Excel sheet:

1. Selection
2. Cell
3. Row
4. Column
5. Area
6. Offset
7. 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.

Share on Facebook

Tweet