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