Simplify a list of excel cells to create a Range

Question

I want to create a Excel.Range object from a list of cell references which are dynamically generated.

Excel.Range outputRange = sheet.get_Range(strCellRange, Type.Missing);

Since the strCellRange can get quite large, it gives com exceptions. Therefore I want to simplify it to have range notation with union.

e.g.

string strCellRange = "F2,G2,H2,I2,J2,K2,L2,F7,G7,H7,I7,J7,K7,L7,F12,G12,H12,I12,J12,K12,L12,F17,G17,H17,I17,J17,K17,L17,F22,G22,H22,I22,J22,K22,L22,F27,G27,H27,I27,J27,K27,L27";

to

string strCellRange = "F2:L2,F7:L7,F12:L12,F17:L17,F22:L22,F27:L27";

1. Is there any Excel method to create a Range object with lot of cell references?
2. Is there a known algorithm to achieve above simplification (a matrix algorithm)?

Answer 1

Gayan,

In VBA you could force your direct string to a range with

Sub Test()
Dim rng1 As Range
Dim strCellRange As String
strCellRange = "F2,G2,H2,I2,J2,K2,L2,F7,G7,H7,I7,J7,K7,L7,F12,G12,H12,I12,J12,K12,L12,F17,G17,H17,I17,J17,K17,L17,F22,G22,H22,I22,J22,K22,L22,F27,G27,H27,I27,J27,K27,L27"
Set rng1 = Range(strCellRange)
Set rng1 = Union(rng1, rng1)
Debug.Print rng1.Address
End Sub