TRIMRANGE
Description
Removes empty rows and columns from the edges of a range of data or array.
This function is useful for adjusting the range size of dynamic datasets.
Syntax
TRIMRANGE(range,[trim_rows],[trim_columns])
- range: The range or array to be trimmed.
- trim_rows: A number that determines how rows should be trimmed. Options include:
- 0: None.
- 1: Trims leading blank rows.
- 2: Trims trailing blank rows.
- 3 (default): Trims both leading and trailing blank rows.
- trim_columns: A number that determines how columns should be trimmed. Options include:
- 0: None.
- 1: Trims leading blank columns.
- 2: Trims trailing blank columns.
- 3 (default): Trims both leading and trailing blank columns.
Trim References
A trim reference operator can be used to achieve the same functionality as TRIMRANGE by adding a period before and/or after the colon in the reference.
Type | Example | Equivalent TRIMRANGE | Description |
|---|---|---|---|
Trim All (.:.) | A1.:.E10 | TRIMRANGE(A1:E10,3,3) | Trim leading and trailing blanks. |
Trim Trailing (:.) | A1:.E10 | TRIMRANGE(A1:E10,2,2) | Trim trailing blanks. |
Trim Leading (.:) | A1.:Z10 | TRIMRANGE(A1:E10,1,1) | Trim leading blanks. |
This pattern can also be applied to full-column or full-row references (eg. A:.A). For example: The formula =SUM(A:.A) will sum only the values in column A, ignoring blank rows below.