How do I assign the following into a variable?

[Oberon70]

I am trying to figure out how to code VBA to consolidate a range on one of my sheets.

The sheet would have the following on it:

and once consolidated it would look like (not consolidating the 7101)

The size of the above data can be different from each report, but it is always A1:D and then lRow is my variable that the number of rows are assigned to.
I came across the website with information on consolidating.

Consolidated Data Link

and thought I ad to convert my range address to R1C1 and found the below:

Msgbox Application.ConvertFormula(Formula:="A2:" & "D" & lRow, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1)

However, I don't know how I take this part Application.ConvertFormula(Formula:="A2:" & "D" & lRow, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1) and assign it to a variable

or if there is a better way to accomplish the above?

 

[Micron]

or if there is a better way to accomplish the above

Sum columns in a dynamic named range - no code?
I forget if the dynamic range has to be at the sheet or workbook level, but I might still have a copy of a file that uses a dynamic range, or you could google it for the details.