Home > Articles > Home & Office Computing > Microsoft Applications

  • Print
  • + Share This
  • 💬 Discuss

This chapter is from the book

Using the Top-Left and Bottom-Right Corners of a Selection to Specify a Range

There are two acceptable syntaxes for the Range command. To specify a rectangular range in the first syntax, you specify the complete range reference just as you would in a formula in Excel:

Range("A1:B5").Select

In the alternative syntax, you specify the top-left corner and bottom-right corner of the desired rectangular range. In this syntax, the equivalent statement might be

Range("A1", "B5").Select

For either corner, you can substitute a named range, the Cells function, or the ActiveCell property. This line of code selects the rectangular range from A1 to the active cell:

Range("A1", ActiveCell).Select

The following statement would select from the active cell to five rows below the active cell and two columns to the right:

Range(ActiveCell, ActiveCell.Offset(5, 2)).Select

Shortcut for Referencing Ranges

A shortcut is available when referencing ranges. It uses [square brackets], as shown in Table 3.1:

Table 3.1 Shortcuts for Referring to Ranges

Standard Method

Shortcut

Range("D5")

[D5]

Range("A1:D5")

[A1:D5]

Range ("A1:D5," "G6:I17")

[A1:D5, G6:I17]

Range("MyRange")

[MyRange]


Named Ranges

You've probably already used Named ranges on your sheets and in formulas. You can also use them in VBA.

To refer to the range "MyRange" in Sheet1, do this:

Worksheets(1).Range("MyRange")

Notice that the Name of the range is in quotes—unlike the use of Named ranges in formulas on the sheet itself. If you forget to put the Name in quotes, Excel thinks you are referring to a variable in the program, unless you are using the shortcut syntax discussed in the previous section, in which case, quotes are not used.

  • + Share This
  • 🔖 Save To Your Account
VBA and Macros for Microsoft Excel

This chapter is from the book

VBA and Macros for Microsoft Excel

Discussions

comments powered by Disqus