Happy Codings - Programming Code Examples
Html Css Web Design Sample Codes CPlusPlus Programming Sample Codes JavaScript Programming Sample Codes C Programming Sample Codes CSharp Programming Sample Codes Java Programming Sample Codes Php Programming Sample Codes Visual Basic Programming Sample Codes


Visual Basic Programming Code Examples

Visual Basic > Other Code Examples

Determining the extent of an Excel Range

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102
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