# TRIMRANGE

## Content

This function excludes all empty rows and/or columns from the outer edges of a range or array.​​​​​​​​​​​​​​

## Syntax

TRIMRANGE(range,[trim\_rows],[trim\_cols])

## Arguments

The TRIMRANGE function scans in from the edges of a range or array until it finds a non-blank cell (or value), then excludes those blank rows or columns.

| @cols=1:**Argument** | @cols=1:**Description** |
| -------- | ----------- |
| @cols=1:@rows=1:*range* | @cols=1:@rows=1:[Required] The range (or array) to be trimmed. |
| @cols=1:@rows=1:*trim\_rows*<br>  | @cols=1:@rows=1:[Optional] Determines which rows should be trimmed.<br>**0** – None<br>**1** – Trims leading blank rows<br>**2** – Trims trailing blank rows<br>**3** – Trims both leading and trailing blank rows (default) |
| @cols=1:@rows=1:*trim\_columns* | @cols=1:@rows=1:[Optional] Determines which columns should be trimmed.<br>**0** – None<br>**1** – Trims leading blank columns<br>**2** – Trims trailing blank columns<br>**3** – Trims both leading and trailing blank columns (default) |

## Remarks

Return: RangeReference. A trimmed range.
TRIMRANGE does not work with 3D references like **=Sheet1:Sheet3!A:A**. Spread.NET will auto-correct the formula `=SUM(Sheet1:Sheet3!A.:.A)` to `=SUM(Sheet1:Sheet3!A:A)`
The `.:.` `:.` `.:` are not operators, so the `=A1.:.INDIRECT("A10")` will be an invalid formula. And the `=TRIMRANGE(A1:INDIRECT("A10"))` can be used.

## Trim References

Trim References (aka Trim Refs) are a set of shorthand notations that allow you to trim blank rows and columns from a range without having to use the TRIMRANGE function explicitly.
Trim Refs modify the traditional colon (`:`) range operator by adding dots (`.`) to specify how you want to trim the range. There are three types of Trim Refs:

| @cols=1:**Trim Ref Type** | @cols=1:**Syntax** | @cols=1:**Equivalent TRIMRANGE** | @cols=1:**Description** |
| ------------- | ------ | -------------------- | ----------- |
| @cols=1:@rows=1:Trim All | @cols=1:@rows=1:`A1.:.E10` | @cols=1:@rows=1:`=TRIMRANGE(A1:E10, 3, 3)` | @cols=1:@rows=1:Trims both leading and trailing blanks from the range. |
| @cols=1:@rows=1:Trim Trailing | @cols=1:@rows=1:`A1:.E10` | @cols=1:@rows=1:`=TRIMRANGE(A1:E10, 2, 2)` | @cols=1:@rows=1:Trims only the trailing blanks from the range. |
| @cols=1:@rows=1:Trim Leading | @cols=1:@rows=1:`A1.:E10` | @cols=1:@rows=1:`=TRIMRANGE(A1:E10, 1, 1)` | @cols=1:@rows=1:Trims only the leading blanks from the range. |

## Examples

`TRIMRANGE(A1:E10, 3, 3)`
`TRIMRANGE(A1:E10, 2, 2)`

## Version Available

This function is available in product version 19.0 or later.