Databases Reference
In-Depth Information
FIGURE 1.6
Simultaneous selection of two ranges in Excel.
It is also possible to select multiple areas on a Workbook simultaneously. This is done by grouping
the addresses in a single Range statement and separating them with commas. This statement
Range("B8:D10, F8:H10").Select
will select two rectangular ranges on a Worksheet — one with corners at row 8 col 2
×
row 10 col
4, and the other at row 8 col 6
row 10 col 8, as shown in Figure 1.6.
Although it is possible to reference ranges in Worksheets using the (column letter)(row number)
as shown in the preceding text, the method, on its face, is severely limited in that ranges cannot
be dynamically defined using variables as with the Cells form of the Range method. Taking this
method of selecting Ranges in the examples presented, its only real use in programming would be
to select areas in Worksheets where parameters would be fixed in the same location on every
Worksheet. This is seldom the case because reports will often contain varying numbers of samples
run, and even the report parameters themselves can be altered depending on the selections made
at the beginning of a macro program. However, there is a trick that can be employed that makes
the A1 style [(column letter)(row number)] of referencing more flexible to use.
Notice that in all the examples presented using the A1 style of the Range method, the actual
Range reference is passed as a string. It is possible to replace the Range reference with a string
variable that represents a valid Range. Therefore, in the statement
×
Range("B2:D4").Select
the Range reference of “B2:D4” can be represented with a string variable. The statement could be
written with the same functionality using the following two lines:
StrRange$ = "B2:D4"
Range(StrRange$).Select
Here, a valid range is replaced with the string variable
This is more forcefully
StrRange$.
illustrated using the following subroutine:
Sub ReplaceStrInRange(ByVal Index As Integer)
Dim StrRange$
Select Case Index
Case 1
StrRange$ = "A1"
Case 2
StrRange$ = "B2:D4"
