TRIMRANGE

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.

Argument	Description
range Required	The range (or array) to be trimmed
trim_rows [Optional]	Determines which rows should be trimmed 0 - None 1 - Trims leading blank rows 2 - Trims trailing blank rows 3 - Trims both leading and trailing blank rows (default)
trim_columns [Optional]	Determines which columns should be trimmed 0 - None 1 - Trims leading blank columns 2 - Trims trailing blank columns 3 - Trims both leading and trailing blank columns (default)

Argument

Description

range

Required

The range (or array) to be trimmed

trim_rows

[Optional]

Determines which rows should be trimmed

0 - None

1 - Trims leading blank rows

2 - Trims trailing blank rows

3 - Trims both leading and trailing blank rows (default)

trim_columns

[Optional]

Determines which columns should be trimmed

0 - None

1 - Trims leading blank columns

2 - Trims trailing blank columns

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. SpreadJS will auto-correct the formula =SUM(Sheet1:Sheet3!A.:.A) to =SUM(Sheet1:Sheet3!A:A)

TRIMRANGE cannot be used directly to create a dynamic Pivot Table.

The .:. :. .: are not operators, so the =A1.:.INDIRECT("A10") will be an invalid formula. And the =TRIMRANGE(A1:INDIRECT("A10")) can be used.

Breaking Change
Before V18.1.0, The A1. is a valid custom name, hence the formula =A1.:A10 means the range between the custom name A1. and A10.
V18.1.0+, The formula =A1.:A10 evaluation is changed to trim the A1 to A10 range.
=A1.:AAA is a valid formula in the previous version, but will be a parse error in v18.1.0+

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:

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

Examples

Case 1: Use TRIMRANGE for auto expand.

Case 2: Use TRIMRANGE in the data validation list for auto-adding items.

Case 3: Use Trim Refs to fetch the range size