Excel VBA Range Cells method

This Cells method provides another way to refer to ranges and is best used by referring to rows and columns by numbers in the Excel VBA code.  The Cells method comes in 2 flavors:  single cell reference and multi-cell references.

Tips:

·         The row offset value can NOT be zero

·         The column offset value can NOT be zero

·         The offset values (arguments) are 1-based

·         If you make an invalid range address, the Excel VBA error message:  Run-time error ‘1004’:  Application-defined or object -defined error

·         The Range keyword is needed for the multi-cell version

How to use the Cells method

Single cell reference for Cells method

Ex
1	Activesheet.Cells(1,1).Value="Hello"	Range on current worksheet
2	ActiveWorkbook.Worksheets(2).Cells(3,1).value="Test"	Use a range in another worksheet but in the same workbook
3	? Workbooks("Log of type of blog posts.xlsx").Worksheets(1).Cells(1,1).value	Select a range in another workbook

Multi-cell reference for Cells method

You can also refer to a range by specifying the upper left (first argument) and the lower right last argument) cells' arguments

ActiveSheet.Range(ActiveSheet.Cells(3, 1), ActiveSheet.Cells(4, 1))

Another cute way is to have two range variable define the boundaries of the Cells method.  For example, the following code is very transparent as two different range variable define the boundaries of the Cells method. 

Dim objTopLeftCell As Range

Dim objBottomRightCell As Range

Dim objRange As Range

'Step 1: instantiate object variables

Set objTopLeftCell = ActiveSheet.Cells(1, 1)

Set objBottomRightCell = ActiveSheet.Cells(5, 5)

'step 2: create a range using the Cells method

Set objRange = ActiveSheet.Range(objTopLeftCell, objBottomRightCell)

objRange.Select

See also

Cells method on MSDN

Naming an Excel.Range

Although there are several different ways to name ranges, the simplest and the most efficient is to:

1.       Select a range

2.       Define a name : worksheet level or entire workbook level using the name property of the range object

How to name a range

Named ranges are part of the Workbook’s names collection and support two levels of names: worksheet  and  workbook.  After selecting a range, the next step is to name the range. To make a workbook level name,  just include the name in the name property of the selected range object.  Making a worksheet level name is a little more complex as the range’s name property includes the worksheet name plus the exclamation point and the desired name.   Check out these examples for clarity.

Example	Note
Range("A1:E5").Name="Header2"	Workbook name
Range("A1:A4").Name="Sheet1!Col_team2"	Worksheet specific name

 

How to use ranges with names

The names that you created – either workbook or worksheet specific are added to the workbook’s names collection.  Again- since this is Excel – there are various ways to refer to ranges by names but the most flexible is to use the RefersToRange property of the Name object.  To retrieve the value of the first cell in the Workbook level name Header2 try: Names("Header2").RefersToRange.Cells(1,1).value.  The main advantages of referring to ranges by names instead of by their cell address  are:

·         Transparency in formula design

·         Flexibility

·         Maintenance free