- Document Solutions for Excel, .NET Edition Overview
- Key Features
- Getting Started
- Features
- Templates
-
Formula Reference
- Add-in and Automation Functions
- Compatibility Functions
- Database Functions
- Date and Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Logical Functions
-
Lookup and Reference Functions
- ADDRESS
- AREAS
- CHOOSE
- CHOOSECOLS
- CHOOSEROWS
- COLUMN
- COLUMNS
- DROP
- EXPAND
- FILTER
- FORMULATEXT
- GETPIVOTDATA
- GROUPBY
- HLOOKUP
- HSTACK
- HYPERLINK
- IMAGE
- INDEX
- INDIRECT
- LOOKUP
- MATCH
- OFFSET
- PIVOTBY
- ROW
- ROWS
- SINGLE
- SORT
- SORTBY
- TAKE
- TOCOL
- TOROW
- TRANSPOSE
- TRIMRANGE
- UNIQUE
- VLOOKUP
- VSTACK
- WRAPCOLS
- WRAPROWS
- XLOOKUP
- XMATCH
- Math and Trigonometry Functions
- Statistical Functions
- Text Functions
- Web Functions
- File Operations
- Document Solutions Data Viewer
- API Reference
- Release Notes
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) |
Remarks
Return: RangeReference. A trimmed range.
TRIMRANGE does not work with 3D references like =Sheet1:Sheet3!A:A. DsExcel.NET 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.
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 |
|
| Trims both leading and trailing blanks from the range. |
Trim Trailing |
|
| Trims only the trailing blanks from the range. |
Trim Leading |
|
| Trims only the leading blanks from the range. |
Examples
TRIMRANGE(A1:E10, 3, 3)
TRIMRANGE(A1:E10, 2, 2)