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