How to subtract combined named ranges with VBA

Question

I cannot find a way to subtract data in my named ranges. I have created three named ranges in VBA according to the following screen:

I have three named ranges. What I am trying to do is to create ONE named range consisting of Data_1 and Data_2 and then subtract Data_3 from it.

I have tried something like this:

Thisworkbook.names.add Name:="Data1and2", RefersTo:="=(" & Data_1 & "," & Data_2 & ")"
Thisworkbook.names.add Name:="FinalResult", RefersTo:="=" & Data1and2 & "-" & Data_3

This seems to be working, no error whatsoever, but the result is a blank array of 0's. When I subtract, for example, Data_3 from Data_1 (so no combined ranges) then it is working fine, but as soon as I combine two ranges into one named range, it stops working.

I have also tried to reference the ranges directly, not by name but address but it does not work either.

Any ideas?

EDIT: I have changed the picture to show the problem better. The ranges Data_1 and Data_2 might not be adjacent and continuous, that is what makes this issue problematic for me. I need to somehow combine them into one range. One of those ranges can also be in one column, while the other will be in one row (transposed).

Answer 1

To create FinalResult in VBA, assuming your three Data ranges already exist:

.Add Name:="FinalResult", RefersTo:="=Data_1:Data_2-Data_3"

Which is the same formula you would enter doing it manually on the worksheet.

Edit

In your revised question, you now have non-contiguous ranges, also possibly of different shapes, for data_1 and data_2.

If you have a modern version of windows desktop Excel, with the TEXTJOIN and FILTERXML functions, you can use the following formula:

FinalResult Refers To:  =FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,Data_1,Data_2) & "</s></t>","//s")-Data_3

If Data_3 does not have the same number of cells as do Data_1 and Data_2, errors will be returned, which you can test for.

So, your relevant code snippet would be:

   RefersTo:= "=FILTERXML(""<t><s>"" & TEXTJOIN(""</s><s>"",TRUE,Data_1,Data_2) & ""</s></t>"",""//s"")-Data_3"

or

    RefersTo:= "=IFERROR(FILTERXML(""<t><s>"" & TEXTJOIN(""</s><s>"",TRUE,Data_1,Data_2) & ""</s></t>"",""//s"")-Data_3,"""")"