VBA Excel Range

In VBA Excel Range can be used in three basic ways.

The first way is to directly refer to the Range. For example...

Range("B5:E8").Select

Range("B5:E8").Value

or whatever Method or Property you choose.

When another Worksheet is active, you qualify the range with the Worksheet object. Either by Name...

Sheets("Sheet3").Range("B5:E8").Border

or by its index...

Sheets(1).Range("B5:E8").Border

And when another Workbook is active you qualify the Sheet with the Workbook. Either by Name...

Workbooks("MyWorkbookName").Sheets("Sheet3").Range("B5:E8").Value

or by its index...

Workbooks(2).Sheets("Sheet3").Range("B5:E8").Value

Using the Cells Method

Cells(2,5).Select

Would select the Cell or Range("B5")

You can also specify a range of more than one Cell...

Range(Cells(2,5), Cells(5,8))

Qualify the Range where needed as above.

Using the Cells Method is obviously much easier to use while looping though rows and columns of cells, since you can use the counters directly without converting the column to a letter(s) and the row to a string to look like "B5", for example.

Using the Offset Method

This is like using the Cells Method except that you choose which Cell your row and column numbers are Offsetting from instead of an imaginary Cell(0,0) in the upper left corner.

Range("E5").Offset(1,1).Value

This would reference Cell "F6".

This site is powered by Site Build It!. If you enjoy it, please check out the
Site Build It homepage to learn more and on how to build a success-guaranteed site with no technical skills.

Custom Search

Return from VBA Excel Range to VBA Code Samples

Return to our Homepage

SBI!