Tag Archives: MergedCells

Excel VBA Range Tutorial

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 5.00 out of 5)

The VBA Range Object

The Excel Range Object is an object in Excel VBA that represents a cell, row, column, a selection of cells or a 3 dimensional range. The Excel Range is also a Worksheet property that returns a subset of its cells.

worksheet range
worksheet cells
range rows and columns
range cut paste
range clear
traverse ranges
range addresses
merged ranges
named ranges

Worksheet Range

The Range is a Worksheet property which allows you to select any subset of cells, rows, columns etc.

Select a cell or Range of cells using the Select method. It will be visibly marked in Excel:

range select cell
Select a single cell using the Range object

Working with Range variables

The Range is a separate object variable and can be declared as other variables. As the VBA Range is an object you need to use the Set statement:

The Range object defaults to your ActiveWorksheet. So beware as depending on your ActiveWorksheet the Range object will return values local to your worksheet:

You might want to define the Worksheet reference by Range if you want your reference values from a specifc Worksheet:

The ActiveWorkbook is not same to ThisWorkbook. Same goes for the ActiveSheet. This may reference a Worksheet from within a Workbook external to the Workbook in which the macro is executed as Active references simply the currently top-most worksheet. Read more here

Range properties

The Range object contains a variety of properties with the main one being it’s Value and an the second one being its Formula.

A Range Value is the evaluated property of a cell or a range of cells. For example a cell with the formula =10+20 has an evaluated value of 20.
A Range Formula is the formula provided in the cell or range of cells. For example a cell with a formula of =10+20 will have the same Formula property.

Other Range properties include:
Work in progress

Worksheet Cells

A Worksheet Cells property is similar to the Range property but allows you to obtain only a SINGLE CELL, based on its row and column index. Numbering starts at 1:

cells select cell
Select a single Cell using the Cells property

The Cells property is in fact a Range object not a separate data type.
Excel facilitates a Cells function that allows you to obtain a cell from within the ActiveSheet, current top-most worksheet.

Cells are Ranges which means they are not a separate data type:

Range Rows and Columns

As we all know an Excel Worksheet is divided into Rows and Columns. The Excel VBA Range object allows you to select single or multiple rows as well as single or multiple columns. There are a couple of ways to obtain Worksheet rows in VBA:

Getting an entire row or column

entirerow range
Range EntireRow property
To get and entire row of a specified Range you need to use the EntireRow property. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the row number. Row indexing starts at 1.
entirecolumn range
Range EntireColumn property
To get and entire column of a specified Range you need to use the EntireColumn property. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the column number. Column indexing starts at 1.

The three properties EntireRow/EntireColumn, Rows/Columns and Row/Column are often misunderstood so read through to understand the differences.

Get a row/column of a specified range

range rows function
Range Rows function
If you want to get a certain row within a Range simply use the Rows property of the Worksheet. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the row number. Row indexing starts at 1.
range columns function
Range Columns property
Similarly you can use the Columns function to obtain any single column within a Range. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex actually the first argument you provide will be the column index. Column indexing starts at 1.

To get a range of rows/columns you need to use the Range function like so:

Get row/column of specified range

The above approach assumed you want to obtain only rows/columns from the ActiveSheet – the visible and top-most Worksheet. Usually however, you will want to obtain rows or columns of an existing Range. Similarly as with the Worksheet Range property, any Range facilitates the Rows and Columns property.

Getting a Ranges first row/column number

Aside from the Rows and Columns properties Ranges also facilitate a Row and Column property which provide you with the number of the Ranges first row and column.

Converting Column number to Excel Column

This is an often question that turns up – how to convert a column number to a string e.g. 100 to “CV”.

Range Cut/Copy/Paste

Cutting and pasting rows is generally a bad practice which I heavily discourage as this is a practice that is moments can be heavily cpu-intensive and often is unaccounted for.

Copy function

Range copy function
Range copy function
The Copy function works on a single cell, subset of cell or subset of rows/columns.

The Copy function can also be executed without an argument. It then copies the Range to the Windows Clipboard for later Pasting.

Cut function

range cut function
Range Cut function
The Cut function, similarly as the Copy function, cuts single cells, ranges of cells or rows/columns.

The Cut function can be executed without arguments. It will then cut the contents of the Range and copy it to the Windows Clipboard for pasting.

Cutting cells/rows/columns does not shift any remaining cells/rows/columns but simply leaves the cut out cells empty

PasteSpecial function

range pastespecial function
Range PasteSpecial function
The Range PasteSpecial function works only when preceded with either the Copy or Cut Range functions. It pastes the Range (or other data) within the Clipboard to the Range on which it was executed.


The PasteSpecial function has the following syntax:

The PasteSpecial function can only be used in tandem with the Copy function (not Cut)

The part of the Range which is to be pasted. This parameter can have the following values:

Parameter Constant Description
xlPasteSpecialOperationAdd 2 Copied data will be added with the value in the destination cell.
xlPasteSpecialOperationDivide 5 Copied data will be divided with the value in the destination cell.
xlPasteSpecialOperationMultiply 4 Copied data will be multiplied with the value in the destination cell.
xlPasteSpecialOperationNone -4142 No calculation will be done in the paste operation.

xlPasteSpecialOperationSubtract 3 Copied data will be subtracted with the value in the destination cell.

The paste operation e.g. paste all, only formatting, only values, etc. This can have one of the following values:

Name Constant Description
xlPasteAll -4104 Everything will be pasted.
xlPasteAllExceptBorders 7 Everything except borders will be pasted.
xlPasteAllMergingConditionalFormats 14 Everything will be pasted and conditional formats will be merged.
xlPasteAllUsingSourceTheme 13 Everything will be pasted using the source theme.
xlPasteColumnWidths 8 Copied column width is pasted.
xlPasteComments -4144 Comments are pasted.
xlPasteFormats -4122 Copied source format is pasted.
xlPasteFormulas -4123 Formulas are pasted.
xlPasteFormulasAndNumberFormats 11 Formulas and Number formats are pasted.
xlPasteValidation 6 Validations are pasted.
xlPasteValues -4163 Values are pasted.
xlPasteValuesAndNumberFormats 12 Values and Number formats are pasted.

If True then blanks will not be pasted.

Transpose the Range before paste (swap rows with columns).

PasteSpecial Examples

Below an example where the Excel Range A1:C3 values are copied an added to the E1:G3 Range. You can also multiply, divide and run other similar operations.

PasteSpecial example - Copy and Add
PasteSpecial example – Copy and Add


The Paste function allows you to paste data in the Clipboard to the actively selected Range. Cutting and Pasting can only be accomplished with the Paste function.

Range Clear/Delete

The Clear function

The Clear function clears the entire content and formatting from an Excel Range. It does not, however, shift (delete) the cleared cells.

Excel Range Clear function example
Excel Range Clear function example

The Delete function

Range Delete function
Range Delete function
The Delete function deletes a Range of cells, removing them entirely from the Worksheet, and shifts the remaining Cells in a selected shift direction.
Although the manual Delete cell function provides 4 ways of shifting cells. The VBA Delete Shift values can only be either be xlShiftToLeft or xlShiftUp.

Excel Range Delete - shifting cells
Excel Range Delete – shifting cells

Traversing Ranges

Traversing cells is really useful when you want to run an operation on each cell within an Excel Range. Fortunately this is easily achieved in VBA using the For Each or For loops.

Although this may not be obvious, beware of iterating/traversing the Excel Range using a simple For loop. For loops are not efficient on Ranges. Use a For Each loop as shown above. This is because Ranges resemble more Collections than Arrays. Read more on For vs For Each loops here

Traversing the UsedRange

Excel Range - Worksheet UsedRange
Excel Range – Worksheet UsedRange
Every Worksheet has a UsedRange. This represents that smallest rectangle Range that contains all cells that have or had at some point values. In other words if the further out in the bottom, right-corner of the Worksheet there is a certain cell (e.g. E8) then the UsedRange will be as large as to include that cell starting at cell A1 (e.g. A1:E8). In Excel you can check the current UsedRange hitting CTRL+END. In VBA you get the UsedRange like this:

You can traverse through the UsedRange like this:

The UsedRange is a useful construct responsible often for bloated Excel Workbooks. Often delete unused Rows and Columns that are considered to be within the UsedRange can result in significantly reducing your file size. Read also more on the XSLB file format here

Range Addresses

The Excel Range Address property provides a string value representing the Address of the Range.

Excel Range Address property
Excel Range Address property


Below the syntax of the Excel Range Address property:


Optional. If True returns the row part of the reference address as an absolute reference. By default this is True.

Optional. If True returns the column part of the reference as an absolute reference. By default this is True.

Optional. The reference style. The default value is xlA1. Possible values:

Constant Value Description
xlA1 1 Default. Use xlA1 to return an A1-style reference
xlR1C1 -4150 Use xlR1C1 to return an R1C1-style reference

Optional. If True then property will return an external reference address, otherwise a local reference address will be returned. By default this is False.

Provided RowAbsolute and ColumnAbsolute are set to False, and the ReferenceStyle is set to xlR1C1, then you must include a starting point for the relative reference. This must be a Range variable to be set as the reference point.

Merged Ranges

Excel Range Merge function
Excel Range Merge function
Merged cells are Ranges that consist of 2 or more adjacent cells. To Merge a collection of adjacent cells run Merge function on that Range.

The Merge has only a single parameter – Across, a boolean which if True will merge cells in each row of the specified range as separate merged cells. Otherwise the whole Range will be merged. The default value is False.

Merge examples

To merge the entire Range:

This will result in the following:

Excel Range Merged cells
Excel Range Merged cells

To merge just the rows set Across to True.

This will result in the following:

Excel Range Merged cells across rows
Excel Range Merged cells across rows

Remember that merged Ranges can only have a single value and formula. Hence, if you merge a group of cells with more than a single value/formula only the first value/formula will be set as the value/formula for your new merged Range

Checking if Range is merged

To check if a certain Range is merged simply use the Excel Range MergeCells property:

The MergeArea

The MergeArea is a property of an Excel Range that represent the whole merge Range associated with the current Range. Say that $B$2:$C$3 is a merged Range – each cell within that Range (e.g. B2, C3..) will have the exact same MergedArea. See example below:

Named Ranges

Named Ranges are Ranges associated with a certain Name (string). In Excel you can find all your Named Ranges by going to Formulas->Name Manager. They are very useful when working on certain values that are used frequently through out your Workbook. Imagine that you are writing a Financial Analysis and want to use a common Discount Rate across all formulas. Just the address of the cell e.g. “A2”, won’t be self-explanatory. Why not use e.g. “DiscountRate” instead? Well you can do just that.

Creating a Named Range

Named Ranges can be created either within the scope of a Workbook or Worksheet:

This gives you flexibility to use similar names across multiple Worksheets or use a single global name across the entire Workbook.

Listing all Named Ranges

You can list all Named Ranges using the Name Excel data type. Names are objects that represent a single NamedRange. See an example below of listing our two newly created NamedRanges:


SpecialCells are a very useful Excel Range property, that allows you to select a subset of cells/Ranges within a certain Range.


The SpecialCells property has the following syntax:


The type of cells to be returned. Possible values:

Constant Value Description
xlCellTypeAllFormatConditions -4172 Cells of any format
xlCellTypeAllValidation -4174 Cells having validation criteria
xlCellTypeBlanks 4 Empty cells


Cells containing notes
xlCellTypeConstants 2 Cells containing constants
xlCellTypeFormulas -4123 Cells containing formulas
xlCellTypeLastCell 11 The last cell in the used range
xlCellTypeSameFormatConditions -4173 Cells having the same format
xlCellTypeSameValidation -4175 Cells having the same validation criteria
xlCellTypeVisible 12 All visible cells

If Type is equal to xlCellTypeConstants or xlCellTypeFormulas this determines the types of cells to return e.g. with errors.

Constant Value
xlErrors 16
xlLogical 4
xlNumbers 1
xlTextValues 2

SpecialCells examples

Get Excel Range with Constants

This will return only cells with constant cells within the Range C1:C3:

Search for Excel Range with Errors