VB and VBA Users Source Code: Determining the extent of an Excel Range
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Determining the extent of an Excel Range
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Wednesday, June 13, 2001
Hits:
1201
Category:
Office
Article:
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. 'Author : Andrew Baker 'Date : 25/05/2001 'Notes : 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. 'Author : Andrew Baker 'Date : 25/05/2001 'Notes : 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. 'Author : Andrew Baker 'Date : 25/05/2001 'Notes : 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
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
