CellRange

CellRange
CellRange is a container for cells.
It acts like a Python list, and lets you quickly access many cells at once.

Creating a CellRange

You can create a CellRange in a few different ways:

  1. By name:

    >>> CellRange("A1:A5")
    A1:A5
    >>> CellRange("A1, B3")
    A1, B3
    
  2. With two (row, column) tuples. This returns the box between the two named cells.

    >>> CellRange((1, 1), (5, 1))
    A1, A2, A3, A4, A5
    >>> CellRange((1, 1), (5, "A"))
    A1, A2, A3, A4, A5
    
  3. With a list of (row, column) tuples.

    >>> CellRange([(1, 1), (3, 2)])
    A1, B3
    
  4. With a named range. If “Why_Not_VBA” is the range “NOT1993:NOT1995”, you’ll get

    >>> CellRange("Why_Not_VBA")
    NOT1993, NOT1994, NOT1995
    

    By default, CellRanges are on the active worksheet. To use a different worksheet, pass its name as the first argument.

    If you’re using a named range, it has to be on the same sheet as CellRange (which is the active sheet if you don’t pass one explicitly).

    >>> Cell("Sheet2", "A1:A5")
    A1, A2, A3, A4, A5
    ## These are on Sheet 2
    

Iteration

You can iterate through a CellRange like you would through a list.

>>> for cell in CellRange("A1:B2"):
>>>     print cell.name
A1
B1
A2
B2
>>> len(CellRange("A1:A5"))
5

You can also use brackets to index into a CellRange.

>>> CellRange("A1:A10")[0]
A1

Properties

Extended Cell properties

You can use most Cell properties directly with CellRange. This will pass a list to that property in the CellRange’s Cells.

For example,

>>> CellRange("A1:A5").value = [1, 2, 3, 4, 5]
# sets A1 through A5 to 1 through 5
>>> CellRange("A1:A5").value
[1, 2, 3, 4, 5] # returns the values in the CellRange as a list

You can also pass just one value to a CellRange property. If you do that, each cell in the CellRange will be passed that value:

>>> CellRange("A1:A5").value = 5
>>> CellRange("A1:A5").value
[5, 5, 5, 5, 5]
>>> CellRange("A1:A5").font.bold = True
# bolds the text in A1 through A5

You can get and set value, formula, color, hyperlink, and comment in this way, as well as all the font attributes:

value
formula
color
comment
font.size
font.color
font.bold
font.italic
font.underline
font.strikethrough
font.subscript
font.superscript

You can also get row, col, and position, and font as read-only properties:

>>> CellRange("A1:A5").row
[1, 2, 3, 4, 5]
>>> CellRange("A1:A5").col
[1, 1, 1, 1, 1]
>>> CellRange("A1:A5").position
[(1, 1), (2, 1), (3, 1), (4, 1), (5, 1)]

CellRange Properties

Some CellRange properties don’t map directly to cells.

sheet

The sheet property tells you which sheet all of the cells in the CellRange are on. It’s read-only.

>>> CellRange("Sheet2", "A1:A5").sheet
Sheet2
name

The name of a CellRange is the string you used to create it, or a list of all the cells in the CellRange.

>>> CellRange("A1:A10").name
A1:A10
>>> CellRange("red").name
red
table

table returns a 2-d list (nested list) of values from a rectangular CellRange.

_images/cell_table.png
>>> CellRange("A1:C3").table
[[1, 2, 3], [4, 5, 6], [7, 8, 9]]

CellRange doesn’t have vertical, horizontal, vertical_range, horizontal_range, or table_range properties.

Methods

addition

You can add together CellRanges from the same sheet:

>>> CellRange("A1:B2") + CellRange("B3:B4")
A1, B1, A2, B2, B3, B4
>>> CellRange("Sheet1", "A1") + CellRange("Sheet2", "A2")
## Error
set_name

set_name creates a named range in Excel containing the CellRange. If a named range with the same name already exists, it’ll be overwritten.

>>> CellRange("A1, B2, C3, D4, E5").set_name("diagonal")
>>> CellRange("Diagonal")
A1, B2, C3, D4, E5
is_empty

is_empty returns True if every cell in the range is empty.

>>> CellRange("A1:A5").clear()
>>> CellRange("A1:A5").is_empty()
True
>>> Cell("A2").value = 123
>>> CellRange("A1:A5").is_empty()
False
append

Appending a cell adds it to the CellRange. The cell has to be on the same sheet as the CellRange.

>>> CellRange("A1:A5").append(Cell("B1"))
A1, B1, A2, A3, A4, A5
delete

Deleting a cell removes it from the CellRange.

>>> del CellRange("A1:B2")[0]
B1, A2, B2
clear

clear clears all the values in the CellRange.

>>> CellRange("A1:A5").clear()
## A1, A2, A3, A4, A5 are now empty
copy_from

copy_from copies a Cell or a CellRange to a target CellRange. Copying a Cell will copy it to every cell in the target. If you’re copying one CellRange to another, the lengths of the CellRanges have to match.

>>> CellRange("B1:B5").copy_from(Cell("A1"))
## A1 is copied to B1 through B5
>>> CellRange("B1:B5").copy_from(CellRange("A1:A2"))
## Error - CellRange lengths don't match
>>> CellRange("B1:B5").copy_from(CellRange("A1:A5"))
## A1 through A5 is copied to B1 through B5
copy_format_from

copy_from copies the formatting a Cell or a CellRange to a target CellRange. Copying a Cell will copy it to every cell in the target. If you’re copying one CellRange to another, the lengths of the CellRanges have to match.

>>> CellRange("B1:B5").copy_format_from(Cell("A1"))
## A1 is copied to B1 through B5
>>> CellRange("B1:B5").copy_format_from(CellRange("A1:A5"))
## A1 through A5 is copied to B1 through B5