Sum Non-Continuous Range with VBA

Using Excel VBA to run a macro on multiple non-continuous ranges is possible.  It may be necessary to run a macro on non continuous ranges to get results as the procedure is running.  The following procedure will run through 3 unevenly sized ranges.  It will use a 2D array to store the data and use a simple sum function.  It is an interesting point that;


in VBA is the same as typing the following;

Range("A2", "A51")

I use this technique in the following procedure.

Sub SumIt() 'Excel VBA to sum non sequential ranges.
Dim var As Variant
Dim i As Integer
Dim ar As Variant

ar=[{"A2", "A51"; "A2", "A201"; "A2", "A401"}]

For i=1 To 3 'Start the Loop.
var=Range(ar(i, 1), ar(i, 2))
Range("C" & Rows.Count).End(xlUp)(2)=Application.Sum(var)
Next i
End Sub

The same results could be achieved with the use of excel formulas in cells and the data could be picked up from those cells.  However the procedure is important as it builds on some earlier articles.  It shows how to create a hard coded multi dimensional  array.  

ar=[{"A2", "A51"; "A2", "A201"; "A2", "A401"}]

It also shows how the sum function can easily be used in VBA.


Actually just about all functions which are native to Excel can be transferred across to VBA in the same way.  The following files shows a working example of the above VBA procedure.