VBA: Methods for Referencing Excel Ranges
In Excel, in addition to selecting cells, you can also select groups of cells. A group of cells is called a range.
To select a range:
- Select the cell where you would like.
- Hold the left mouse button.
- Drag to the desired end cell.
- Release the mouse button.
Your selected cells will appear with a bold border around them.
Understanding Range Addresses
Using the name toolbox
Like with cells, you can also select ranges using the name toolbox. Simply enter the address of range you would like to select in the box and press enter.
Refering to Ranges in Visual Basic
There are two main methods for selecting ranges in visual basic:
- Range Method:
- Nesting Cells within Range Method:
Example of the Range Method
This would range "B10:F13"
Nesting Cells within the Range Method
We covered the referring to cells using the cells method in a previous module.
As you will recall the main advantage to using the cells method is a numeric variable can be accepted for either coordinate.
To use the cells method for selecting a range you must know reference the coordinates of the first and last cell in the range separated by a comma.
This would also reference "B10:F13"
Sub RangePractice() 'select range "B10:F13" Range("B10:F13").select End Sub Sub CellsPractice() 'select range "B10:F13" Range(Cells(10,2),Cells(13,6)).select End Sub
Practice Makes Perfect
Practice selecting ranges using both methods. Be prepared in the next module we will look at executing some action on ranges. Click here when you are ready to proceed.