Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
Using the CurrentRegion property is equivalent to choosing the Home➜Editing➜
Find & Select➜Go To Special command and selecting the Current Region option (or by
using the Ctrl+Shift+* shortcut). To see how CurrentRegion selection works, record
your actions while you issue that command. Generally, the CurrentRegion property
setting consists of a rectangular block of cells surrounded by one or more blank rows
If the range to be copied is a table (specified using Insert➜Tables➜Table), you can use code like
this (which assumes the table is named Table1 ):
Range(“Table1[#All]”).Copy Sheets(“Sheet2”).Range(“A1”)End Sub
Tips for working with ranges
When you work with ranges, keep the following points in mind:
● Your code doesn’t need to select a range in order to work with it.
● You can’t select a range that’s not on the active worksheet. So if your code does select a
range, its worksheet must be active. You can use the Activate method of the
Worksheets collection to activate a particular sheet.
● Remember that the macro recorder doesn’t always generate the most efficient code.
Often, you can create your macro by using the recorder and then edit the code to make it
● Using named ranges in your VBA code is a good idea. For example, referring to
Range(“Total”) is better than Range(“D45”) . In the latter case, if you add a row
above row 45, the cell address will change. You would then need to modify the macro so
that it uses the correct range address (D46).
● If you rely on the macro recorder when selecting ranges, make sure that you record the
macro using relative references. Use the Developer➜Code➜Use Relative References
control to toggle this setting.
● When running a macro that works on each cell in the current range selection, the user
might select entire columns or rows. In most cases, you don’t want to loop through every
cell in the selection. Your macro should create a subset of the selection consisting of only
the nonblank cells. See “Looping through a selected range efficiently,” later in this chapter.
● Excel allows multiple selections. For example, you can select a range, press Ctrl, and select
another range. You can test for multiple selections in your macro and take appropriate
action. See “Determining the type of selected range,” later in this chapter.