Hapus baris dan kolom kosong dari suatu rentang
=TRIMRANGE(range, [trim_rows], [trim_columns])| Parameter | Deskripsi |
|---|---|
range |
The range or array to be trimmed. |
trim_rows |
[optional] How rows should be trimmed. 0 = none, 1 = trim leading, 2 = trim trailing, 3 = trim leading and trailing (default). |
trim_columns |
[optional] How columns should be trimmed. 0 = none, 1 = trim leading, 2 = trim trailing, 3 = trim leading and trailing (default). |
The syntax for TRIMRANGE is simple; just give it a range, and it will automatically remove empty rows and columns:
=TRIMRANGE(range) // remove empty rows and columnsThere are two optional arguments, trim_rows, and trim_columns, that let you fine-tune this behavior:
=TRIMRANGE(range,1,1) // remove leading rows and columns
=TRIMRANGE(range,2,2) // remove trailing rows and columns
=TRIMRANGE(range,1,2) // remove leading rows and trailing columnsYou can also trim a range using an alternative syntax based on a "dot operator". When the Excel team added TRIMRANGE to Excel, they also extended the
=A:F // normal range, not trimmed
=A:.F // trim trailing rows and columns
=A.:F // trim leading rows and columns
=A.:.F // trim leading and trailing rows and columnsTRIMRANGE removes empty rows and columns from the outer edges of a range. Starting from the outer boundary of the range, TRIMRANGE scans inward. When
=TRIMRANGE(A:J)The main function of TRIMRANGE, as the name suggests, is to "trim" a range, which means to remove empty rows and columns outside a block of data. You
=TRIMRANGE(G:J)In the worksheet, TRIMRANGE "trims" the range G:J by removing the empty rows above and below the data and returns the result as an array that lands in
=ROWS(TRIMRANGE(G:J)) // returns 12