TRIMGRANGE and a dot

The TRIMRANGE function allows you to remove blank rows or columns before, after, and before and after a set of values within a specified range of cells. It makes it easier to work with cells that are not empty, but recently there is a much simpler syntax for clearing a given range, which is by specifying a dot before the “:” symbol that separates the beginning and end of the range.

If you want to remove all blank values from a given range, you will write a formula:

=TRIMRANGE(A3:A22)

If you remove only blank cells before a set of those that contain a value, the formula should look like this:

=TRIMRANGE(A3:A22,1)

If you’re going to remove only the ones after, write the following statement:

=TRIMRANGE(A3:A22,2)

Can it be simpler? You can, with the help of a “dot” in the address of the range instead of using the TRIMRANGE function. If you are removing all blank values within a range, you need to write the formula:

=A3.:. A22

If you’re only going to remove them, the formula should be:

=A3:. A22

For those of you before:

=A3:A22

You will agree, by applying a formula in which we use a period, before or after the “:” sign to indicate the beginning and end of a range, we can remove blanks from a given range in a much simpler way.