Hello everyone !
Is there a way to immediately or quickly remove all the #REF! values from all SUM formulas (or any formula if applicable) in a worksheet instead of removing them individually ?
Hello everyone !
Is there a way to immediately or quickly remove all the #REF! values from all SUM formulas (or any formula if applicable) in a worksheet instead of removing them individually ?
Hi and welcome to the forum
If you have REF errors in a formula, it probably means you have deleted rows or columns that were being referenced. So, removing them is not necessarily the problem, finding what is causing the problem is where you need to start
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi thank you for the warm welcome
Yes the problem is I have to remove the REF values manually because when I receive the workbook from another person, I need to delete certain values (by removing the row) from the list which results in a lot of REF! in the SUM formulas. I'd figure if there is a faster way to do this it would really save my time for other tasks.
To better clarify what I usually have to do :
Transfer a large list of values between 10-15 lines each with its own sub-totals (SUM formula here) that will be eventually tallied at the bottom with a total (the final SUM formula of the list) that takes the values of the SUM formulas from all the subtotals. I have to delete chunks of subtotals which result in a lot of #REF! for the final SUM formula. I remove the REFs manually after that. Hope that is not too confusing.
Last edited by chaoschaos; 05-14-2014 at 09:29 PM. Reason: Provide additional details
Have you tried, instead of deleting the rows, just deleting the data IN the rows, and leving blank rows? This will eliminate the REF error, and you can hide the empty rows if needed
The values can be deleted but I have to delete the rows as well which results in REFs in the SUM formula. I need to print the list after that
so just deleting the values and leaving gaping holes here and there isnt good for presentation..
My current train of idea is to find a way to keep the SUM formulas intact while removing all the REFs within it.
I did some simple scripting of copying and pasting repeated lines before, but for this one is beyond me at the moment as I am still learning.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks