Excel: Address of Non-Contiguous Range

I have some routines where I want to use the address of cells on another sheet as reference for a Sum formula in VBA. Here is a simple example:

Dim U as range
Dim Sht as worksheet
Dim NewSht as worksheet
Set Sht as Sheets("C3_Report")
Set NewSht = Sheets("Income Expense Summary")
Set U = Union(Sht.range("M24"),Sht.range("M33"),Sht.range("M34"))
NewSht.range("G22") = "=sum(" & U.address(1,1) & ")"
The result for Cell G22 on C3_Expense_report sheet is: =SUM('C3_Report'!$M$24,$M$33:$M$34) This gives me the sum of cell M24 on C3_Report plus the sum of cells M33 and M34 on Income Expense Summary. I need it to be like this: =SUM('C3_Report'!$M$24,'C3_Report'!$M$33:$M$34) I created a roundabout way to fix it, but maybe there is an easier way. Any suggestions?

This question generated 16 answers. To proceed to the answers, click here.

This thread is current as of June 02, 2015.

For more resources for Microsoft Excel