ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

VBA: Methods for Referencing Excel Ranges

Updated on September 17, 2012

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

A range name is composed as follow:

Uppermost Left Cell Address:Bottom Right Cell Address

Lets look at an example:

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:

Range("Range Address")

  • Nesting Cells within Range Method:

Range(cells(row,column),cells(row,column))

Example of the Range Method

Range("B10:F13").select

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.

i.e.

Range(cells(10,2),cells(13,6))

This would also reference "B10:F13"

Practice Macros

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.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.