Excel VBA Ranges and Cells

Associated Files Download Links

Ranges and Cells in VBA

Excel spreadsheets store data in Cells. Cells are arranged into Rows and Columns. Each cell can be identified by the intersection point of it’s row and column (Exs. B3 or R3C2).

An Excel Range refers to one or more cells (ex. A3:B4)

Cell Address

A1 Notation

In A1 notation, a cell is referred to by it’s column letter (from A to XFD) followed by it’s row number(from 1 to 1,048,576).

In VBA you can refer to any cell using the Range Object.

R1C1 Notation

In R1C1 Notation a cell is referred by R followed by Row Number then letter ‘C’ followed by the Column Number. eg B4 in R1C1 notation will be referred by R4C2. In VBA you use the Cells Object to use R1C1 notation:

Range of Cells

A1 Notation

To refer to a more than one cell use a “:” between the starting cell address and last cell address. The following will refer to all the cells from A1 to D10:

R1C1 Notation

To refer to a more than one cell use a “,” between the starting cell address and last cell address. The following will refer to all the cells from A1 to D10:

Writing to Cells

To write values to a cell or contiguous group of cells, simple refer to the range, put an = sign and then write the value to be stored:

Reading from Cells

To read values from cells, simple refer to the variable to store the values, put an = sign and then refer to the range to be read:

Note: To store values from a range of cells, you need to use an Array instead of a simple variable.

Non Contiguous  Cells

To refer to non contiguous  cells use a comma between the cell addresses:

Intersection of  Cells

To refer to non contiguous  cells use a space between the cell addresses:

Offset from a Cell or Range

Using Offset function, you can move the reference from a given Range (cell or group of cells) by the specified number_of_rows, and number_of_columns.

Offset Syntax

Range.Offset(number_of_rows, number_of_columns)

Offset from a cell

Offset from a Range

Setting Reference to a Range

To assign a range to a range variable: declare a variable of type Range then use the Set command to set it to a range. Please note that you must use the SET command as RANGE is an object:

Resize a Range

Resize method of Range object changes the dimension of the reference range:

Top-left cell of the Resized range is same as the top-left cell of the original range

Resize Syntax

Range.Resize(number_of_rows, number_of_columns)

OFFSET vs Resize

Offset does not change the dimensions of the range but moves it by the specified number of rows and columns. Resize does not change the position of the original range but changes the dimensions to the specified number of rows and columns.

All Cells in Sheet

The Cells object refers to all the cells in the sheet (1048576 rows and 16384 columns).

UsedRange

UsedRange property gives you the rectangular range from the top-left cell used cell to the right-bottom used cell of the active sheet.

CurrentRegion

CurrentRegion property gives you the contiguous rectangular range from the top-left cell to the right-bottom used cell containing the referenced cell/range.

Range Properties

You can get Address, row/column number of a cell, and number of rows/columns in a range as given below:

Last Cell in Sheet

You can use Rows.Count and Columns.Count properties with Cells object to get the last cell on the sheet:

Last Used Row Number in a Column

END property takes you the last cell in the range, and End(xlUp) takes you up to the first used cell from that cell.

Last Used Column Number in a Row

END property takes you the last cell in the range, and End(xlToLeft) takes you left to the first used cell from that cell.

You can also use xlDown and xlToRight properties to navigate to the first bottom or right used cells of the current cell.

Cell Properties

Common Properties

Here is code to display commonly used Cell Properties

Cell Font

Cell.Font object contains properties of the Cell Font:

Copy and Paste

Paste All

Ranges/Cells can be copied and pasted from one location to another. The following code copies all the properties of source range to destination range (equivalent to CTRL-C and CTRL-V)

Paste Special

Selected properties of the source range can be copied to the destination by using PASTESPECIAL option:

Here are the possible options for the Paste option:

AutoFit Contents

Size of rows and columns can be changed to fit the contents using the code below:

More Range Examples

It is recommended that you use Macro Recorder while performing the required action through the GUI. It will help you understand the various options available and how to use them.

For Each

It is easier to loop through a range using For Each construct as show below:

At each iteration of the loop one cell in the range is assigned to the variable c and statements in the For loop are executed for that cell. Loop exits when all the cells are processed.

Sort

Sort is a method of Range object. You can sort a range by specifying options for sorting to Range.Sort. The code below will sort the columns A:C based on key in cell C2. Sort Order can be xlAscending or xlDescending. Header:= xlYes should be used if first row is the header row.

Find

Find is also a method of Range Object. It find the first cell having content matching the search criteria and returns the cell as a Range object. It return Nothing if there is no match.

Use FindNext method (or FindPrevious) to find next(previous) occurrence.

Following code will change the font to “Arial Black” for all cells in the range which start with “John”:

Following code will replace all occurrences of  “To Test” to “Passed” in the range specified:

It is important to note that you must specify a range to use FindNext. Also you must provide a stopping condition otherwise the loop will execute forever. Normally address of the first cell which is found is stored in a variable and loop is stopped when you reach that cell again. You must also check for the case when nothing is found to stop the loop.

Range Address

Use Range.Address to get the address in A1 Style

Use xlReferenceStyle (default is xlA1) to get addres in R1C1 style

This is useful when you deal with ranges stored in variables and want to process for certain addresses only.

Range to Array

It is faster and easier to transfer a range to an array and then process the values. You should declare the array as Variant to avoid calculating the size required to populate the range in the array. Array’s dimensions are set to match number of values in the range.

Array to Range

After processing you can write the Array back to a Range. To write the Array in the example above to a Range you must specify a Range whose size matches the number of elements in the Array.

Use the code below to write the Array to the range D1:D5:

Please note that you must Transpose the Array if you write it to a row.

Sum Range

You can use many functions available in Excel in your VBA code by specifying Application.WorkSheetFunction. before the Function Name as in the example above.

Count Range

Written by: Vinamra Chandra