Visual Basic Programming Code Examples

Visual Basic > Other Code Examples

Determining the extent of an Excel Range



Determining the extent of an Excel Range

The following code demonstrates three methods of returning the extent of an Excel Range, i.e all the cells which surround a specific range (equivalent to pressing END+UP ARROW, END+DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW).

Option Explicit

'Purpose   :    Returns a Range object that represents the cells in the column
'               of the region that contains the specified range.
'Inputs    :    [rngSelect]                 The range to return the column region. If not specified, defaults
'                                           to the current selection.
'Outputs   :    Returns the columns in the used range of the specified range.


Function RangeColumnExtent(Optional rngSelect As Excel.Range) As Excel.Range
    If rngSelect Is Nothing Then
        If (Selection Is Nothing) = False Then
            If TypeName(Selection) = "Range" Then
                Set RangeColumnExtent = Selection.Parent.Range(Selection.End(xlDown), Selection.End(xlUp))
            End If
        End If
    Else
        Set RangeColumnExtent = rngSelect.Parent.Range(rngSelect.End(xlDown), rngSelect.End(xlUp))
    End If
End Function


'Purpose   :    Returns a Range object that represents the cells in the row
'               of the region that contains the specified range.
'Inputs    :    [rngSelect]                 The range to return the row region. If not specified, defaults
'                                           to the current selection.
'Outputs   :    Returns the rows in the used range of the specified range.


Function RangeRowExtent(Optional rngSelect As Excel.Range) As Excel.Range

    If rngSelect Is Nothing Then
        If (Selection Is Nothing) = False Then
            If TypeName(Selection) = "Range" Then
                Set RangeRowExtent = Selection.Parent.Range(Selection.End(xlToLeft), Selection.End(xlToRight))
            End If
        End If
    Else
        Set RangeRowExtent = rngSelect.Parent.Range(rngSelect.End(xlToLeft), rngSelect.End(xlToRight))
    End If
End Function

'Purpose   :    Returns a Range object that represents the cells in the region
'               that contains the specified range.
'Inputs    :    [rngSelect]                 The range to return the region around. If not specified, defaults
'                                           to the current selection.
'Outputs   :    Returns all the cells in the used range of the specified range.


Function RangeExtent(Optional rngSelect As Excel.Range) As Excel.Range

    If rngSelect Is Nothing Then
        If (Selection Is Nothing) = False Then
            If TypeName(Selection) = "Range" Then
                Set RangeExtent = Selection.Parent.Range(Selection.Cells(1, 1), Selection.SpecialCells(xlCellTypeLastCell))
            End If
        End If
    Else
        Set RangeExtent = rngSelect.Parent.Range(rngSelect.Cells(1, 1), rngSelect.SpecialCells(xlCellTypeLastCell))
    End If
End Function

'Demonstration routine
Sub Test()
    Dim rngSelection As Excel.Range
    
'---Get the currect column
    Set rngSelection = RangeColumnExtent
    
    'Select and print the address of the range
    rngSelection.Select
    Debug.Print "Current Column: " & rngSelection.Address
    
'---Get the currect row
    Set rngSelection = RangeRowExtent
    
    'Select and print the address of the range
    rngSelection.Select
    Debug.Print "Current Row: " & rngSelection.Address
    
'---Get the currect area
    Set rngSelection = RangeExtent
    
    'Select and print the address of the range
    rngSelection.Select
    Debug.Print "Current Area: " & rngSelection.Address
End Sub