Getting Excel sheet cell value next to a merged column

Question

I have an Excel sheet formatted as the image below.

I would like to retrieve the list of cell texts next to a merged column. I am thinking something of this kind. My idea is to get the total number of merged cells and then iterate over the next set of cells. I cannot find any method in Range that returns the total number of cells in a merged cell. The properties EntireRow and EntireColumn doesn't give the total number of cells merged. Any hints would be helpful.

List<String> GetNextTexts(Microsoft.Office.Interop.Excel.Range range)
{}

Examples

• GetNextTexts(rangeTest) should return {TestRx, TestSPC}
• GetNextTexts(rangeTestRx) should return {P4_7, P0_0, P0_14, P3_2}
• GetNextTexts(rangeTestSPC) should return {P2_4}

Answer 1

Here are some Range properties that can be useful (assuming rangeTest is in B2:B6) :

rangeTest = Range["B2"].MergeArea gives the range of the merged cell Test - B2:B6

rangeTest.Rows.Count and rangeTest.Count give the number of rows and number of cells - 5

rangeTest.Offset[0, 1] gives the range C2, and .MergeArea of that gives the rangeTestRx.

rangeTestRx.Count can be used to get the number of cells in rangeTestRx (4), and compare that to the number of cells in rangeTest to check if there is another cell below rangeTestRx.

rangeTestRx.Offset[4, 0] can be used to get the Range below rangeTestRx - C6 and so on.