VB.net excel merged cells range [duplicate]

Question

This question already has an answer here:

• Get start range and end range of a vertically merged cell with Excel using VBA 6 answers

I have a Excel spreadsheet that hold some sizes. And I would like to search this file at runtime.

This Sheet contains a table column 1 defines the group within the search must occur. So I will search Column A until I find a cell containing the group I prefer.

This group is a merged cell that contains all the rows that belong to this group.

My question: How can I get the range of rows consumed by the merged cell? This range is needed to list all available ratings for this group.

Answer 1

Thanks to the comment by J. Chomel. I could answer this question by myself. This Link showed me all the information I needed.

Thanks!

    Public Sub GetRatings()

    Dim oExcelLoc As String = "J:\EXCEL - Lijsten\NOZZLE SIZES.xlsx"

    ' Excel load data
    Dim oExcelApp As New Application
    Dim oWorkBook As Workbook
    Dim oWorkSheet As Worksheet

    oWorkBook = oExcelApp.Workbooks.Open(oExcelLoc, False, True)
    oWorkSheet = oWorkBook.Worksheets(1)


    For oRow As Integer = 1 To 100
        If oWorkSheet.Range("A" & oRow).Value = GroupString Then

            Dim oRange As Range
            oRange = oWorkSheet.Range("A" & oRow)

            ' Check if merge cells
            If oRange.MergeCells Then

                Debug.Print("Group start row = " & oRow & " |Group end row = " & oRow + oRange.MergeArea.Rows.Count - 1)

            Else
                MsgBox("Error")
            End If
            Exit For
        End If
    Next


    oWorkBook.Close()

    'Release object references.
    releaseObject(oWorkSheet)
    releaseObject(oWorkBook)
    releaseObject(oExcelApp)

End Sub

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub