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
