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

Excel.Range Resize method

Ever need to add rows, delete  columns, and/or subtract rows in an range?   Well, the Resize method of the Excel.Range object is just the tool in Excel's expansive toolbox.   It can either expand or contract an existing range  – by altering the  either the number of columns and/or number of rows contained in the source range.

How to use the Resize method

To use, follow this  syntax found on MSDN

Range object.Resize(Desired Rowsize, Desired Column Size). 

Although, the syntax for using the Resize method seems straightforward, there are some potential gotchas.  To overcome them, follow these tips:

• The ColumnSize argument can’t be zero

• The RowSize argument can’t be zero

• In invalid value will trigger the Excel VBA error: Run-time error ‘1004’ Application-defined or object-defined error

 

Some example are:

Code	Note
range("A1:B2").Name="MyRange"	Create the range MyRange on the activesheet( 2 rows, 2 columns)
names("MyRange").RefersToRange.resize(Columnsize:=3).Select	Expand MyRange to contain 3 columns
names("MyRange").RefersToRange.resize(1).Select	Contract MyRange  to 1 column
Range("b1").resize(-1).select	Purposely generate VBA error 1004

 

See also

http://www.wiseowl.co.uk/blog/s160/resize-method-excel-vba.htm

 

Excel Range Tips, Tricks and Traps

 
1. How to refer to a range using A1 notation
Range("A1:A5").Copy Range("B1")  2. How to put a value in a range(cell)
Range("C1").Value="Hello world"  3. How to create a multi-cell range
Range("A1:G5").Select  4. How to refer to a multi-cell range in a particular worksheet
Worksheets(1).Range("A1,A5,B4:B8").Select  5. How to refer to the currently active cell
? ActiveCell.Address  6. How to find the parent object (worksheet) of a range object
? ActiveCell.Parent.Name  7. How to refer to a range in the workbook that currently has focus 
              ActiveWorkbook.Worksheets(1).Range("A1:C2").Select 

8. How to refer to a range In the workbook that contains the code
ThisWorkbook.Worksheets(1).Range("C2").Select  9. How to add a formula to a range Range("B4").Formula="=45*10"  10. How to determine if a cell has a formula
? ActiveCell.HasFormula

Excel.Range R1C1 cell reference notation

 

Using R1C1 cell reference notation (R1C1) is preferred in Excel VBA rather than the more often seen A1 cell reference notation ($A$1).  The R stands for the row coordinate while the C stands for the column coordinate -  e.g. R2C2 is equivalent  B$2$.  Here are some rules for using the R1C1 cell reference style:

• For rows, positive values refer to cells towards the bottom of the spreadsheet whereas negative values refer to cells towards the top of the spreadsheet
• For columns, positive values refer to cells towards the right side of a spreadsheet and negative values refer to cells towards the left side of a spreadsheet.
• If you leave out a value for either the R or C coordinate,  than it means that you are referring  to the same row or column associated with the current cell location
• The first row or first column is 1

How to Use It

A cell reference can point to a single cell range, a bunch of cells or even entire row or column.   How?

Ex.	Address	A1 notation	R1C1 notation	Note
1		A$2$	=R2C1	Absolute address
2	$F$19	=MAX(C4:C17)	=MAX(R[-15]C[-3]:R[-2]C[-3])	Relative  addresses in formula
3	$F$20	=MIN(C:C)	=MIN(C[-3])	The entire column using relative address
4	$C$4	=ROUND(B4,0)	=ROUND(RC[-1],0)	Relative reference in formula

Tip:

Using CTRL + ` (French accent grave symbol)  keys to toggle from seeing the values (Normal view) to seeing formulas (Formula view) in the active worksheet