The TRIMRANGE function excludes all empty rows and/or columns from the outer edges of a range or array.
Syntax
The TRIMRANGE function scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns.
=TRIMRANGE(range,[trim_rows],[trim_cols])
| Argument | Description |
|---|---|
| range Required |
The range (or array) to be trimmed |
| trim_rows | 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 | 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) |
Trim References (aka Trim Refs)
A Trim Ref can be used to achieve the same functionality as TRIMRANGE more succinctly by replacing the range's colon ":" with one of the three Trim Ref types described below:
| 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 -row references (eg. A:.A)