convert =SUM(A1:A3) to =A1+A3

[Deliverable7]

Hi,
I have a complex filtered list with multiple formula =Sum(up to 30+ cells) that will corrupt as i re-filter my data. Is there a way to change a formula or vba to convert from =SUM(A1:A3) to =A1+A3 etc. I would be looking to restrict the vba to a single column.
Looking at having to manually change around 1,000 cells one a ta time
Thanks
Colin

 

[Fazza]

hi, Colin
Can you use the SUBTOTAL function? It is made for this sort of thing.

If not, please give some further description. Regards, Fazza

 

[Rick Rothstein]

Is there a way to change a formula or vba to convert from =SUM(A1:A3) to =A1+A3 etc. I would be looking to restrict the vba to a single column.

Fazza appears to have given you a good piece of advice, but I wanted to point out to you that what I highlighted in red is not equivalent to what I highlighted in blue. This...

=SUM(A1:A3)

is equivalent to this...

=A1+A2+A3

and not what I highlighted in blue above. Also note that if you have more cells in the range, you would have more values in your summation. I'd try what Fazza has pointed you to instead.

 

[Deliverable7]

Thanks for your feedback Rick and Fazza.
I managed to get the problem sorted.
Cheers