TRIMRANGE function - Zoho Sheet | Help Guide

TRIMRANGE

Removes empty rows and columns from the outer edges of a given range or array.

Syntax


TRIMRANGE(range; [trim_rows]; [trim_columns])

range: The range or array from which you want to remove empty rows and columns. E.g. A1:E10

trim_rows: Determines which empty rows to remove. Defaults to 3 if omitted.

  • 0 - Does not trim any rows.
  • 1 - Removes leading empty rows.
  • 2 - Removes trailing empty rows.
  • 3 - Removes both leading and trailing empty rows.

trim_columns: Determines which empty columns to remove. Defaults to 3 if omitted.

  • 0 - Does not trim any columns.
  • 1 - Removes leading empty columns.
  • 2 - Removes trailing empty columns.
  • 3 - Removes both leading and trailing empty columns.

Examples

Formula

Result

=TRIMRANGE(A1:D8;2;0)

Returns the range A1:D6 by removing trailing empty rows.

=TRIMRANGE(A1:D8;1;0)

Returns the range A2:D8 by removing the leading empty row without trimming any columns.

=TRIMRANGE(A1:D8;0;2)

Returns the range A1:C8 by removing the trailing empty column.

=TRIMRANGE(A1:D8;3;3)

Returns A2:C6 by removing the empty rows and columns from the outer edges of the range. 

Notes
TRIMRANGE only removes empty rows and columns from the outer edges of the range. Empty cells within the data are still retained.


Get a hands-on experience of the function in the embedded range below.

Possible Errors

Errors

Meaning

#N/A!

  1. The function could not find the value it was asked to look for. Commonly occurs in VLOOKUP and similar functions.

#NAME!

  1. The function name is incorrect or invalid.
  2. The given defined name (if any) is invalid.
  3. There is a typo in the defined name used in the function.
  4. Double quotes are missing for text values in the function.
  5. Colon missing in a cell range reference.

#VALUE!

  1. The given argument's data type is incorrect or invalid.
#REF!
  1. The given cell reference is incorrect or invalid. This error may occur when a cell/range has been deleted, moved, or pasted over.
Similar Functions