sum multiple named ranges if they exist

[Guest]

I'm using Excel 2003. I have an excel macro that creates multiple sections.
Each section has multiple columns that are totaled. Each section's totals
row has a name. I want to add a row that sums the first 4 sections. If all
4 sections are in the worksheet, the following code works:

Selection.FormulaR1C1 = "=SUM(MyRange1 + MyRange2 + MyRange3 + MyRange4)

However, if any one (or more) of those sections are missing, it inserts
#REF!. Is there a way that I'm overlooking to have it add only the total
rows that exist?

 

[Guest]

Dim s as String, i as Long
Dim rng as Range
for i = 1 to 4
set rng = Nothing
on error resume next
set rng = Range("MyRange" & i)
on Error goto 0
iF not rng is nothing then
s = s & "MyRange" & i & "+"
end if
Next
s = Left(s,(len(s)-1))

Selection.FormulaR1C1 = "=Sum(" & s & ")"

 

[Guest]

Thank you. I was hoping that there was a way to do this right in the
formula, (such as with the "IsErr" somehow) as the named ranges in the
spreadsheet are not actually MyRange1, etc. but distinctive names. But I was
looking so hard at trying to do that, I didn't think about just feeding the
range names to a variable. That will work. Thanks again.