I'm trying to use trimrefs to improve efficiency.
For example, replacing =SUM(A4:A8) with =SUM(A4.:.A8)
I've noticed that if A4:A8 is blank, it gives a #Ref error rather than zero, which is a problem as in my intended use there's no guarantee that the range will be populated.
IFERROR, maybe?
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Delete Delete
Last edited by JohnTopley; 04-04-2025 at 02:39 PM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Looking at the def of Trimrange..
https://support.office.com/client/re...ain11.chm60727
It looks like you need to have something in the range to get an answer back. Trying to trim a completely blank range doesn't fit with the:
The TRIMRANGE function scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
IFERROR does helpFormula:
=IFERROR(A1.:.A8,"")
Try this in sum function.
=SUM(IFERROR(A4.:.A8,0))
Thanks guys. I was hoping that the trimrefs were able to handle it, but I guess IFERROR is the obvious solution.
You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks