Excel formula to remove the empty rows at the bottom from a range

Question

In Excel, I have a range B4:E100 in a worksheet, assume the content is like the follows. I'm looking for a formula (as simple as possible, with new Excel functions probably) that returns B4:E8, because there is nothing below row 8.

Does anyone know how to write such a formula that takes B4:E100 as input?

Try using TRIMRANGE() reference operators: =LET(a, B4.:.E100, IF(a=0,"",a)) — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Feb 26 at 22:12
May I know what the expected output range is should be? is it B8:E8 or B4:E8? if the latter then. =LET(a, TAKE(B4.:.E100,-1), IF(a=0,"",a)) — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Feb 26 at 22:14
Sorry, the expected output should be B4:E8, i just corrected it in the OP. — SoftTimur
– SoftTimur, Commented Feb 26 at 22:20
Yes, I do. I tried =TRIMRANGE(B4:E00,2), it fills empty cells with 0, how could i avoid that? — SoftTimur
– SoftTimur, Commented Feb 26 at 22:24

Mayukh Bhattacharya · Accepted Answer · 2025-02-26 22:25:31Z

2

Try using the TRIMRANGE() function or its reference operators:

• Formula used in cell G4

=LET(a, B4:.E100, IF(a=0,"",a))

Or,

=LET(a, TRIMRANGE(B4:E100,2), IF(a=0,"",a))

Can read here more on the said function: TRIMRANGE() Function

answered Feb 26 at 22:25

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rotabor Feb 27 at 10:12

Be aware that IF(a=0,"",a) is an extra calculation to use the result as the argument in a formula, thus it should be omitted.

Collectives™ on Stack Overflow

Excel formula to remove the empty rows at the bottom from a range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related