Public Function CountMergedRowsInCell(myRow As Long, myColumn As Long, Optional mySheet As Variant)

'       This function returns a count of the number of rows in a merged cell

'       myRow is required and denotes the row that you want to test.
'       myColumn is required and denotes the column to test.
'       mySheet is optional and is the name of the sheet to be used. If omitted then the active sheet is used

'       Example of use:  RowCount = CountMergedRowsInCell(3 , 1)
'                       This will return the number of rows merged to row 3 in column 1 of the active sheet

If IsMissing(mySheet) Then mySheet = ActiveSheet.Name
CountMergedRowsInCell = 0
myLastRow = ThisWorkbook.Worksheets(mySheet).Cells(Rows.Count, myColumn).End(xlUp).Row
If ThisWorkbook.Sheets(mySheet).Cells(myRow, myColumn).MergeCells = True Then
    CountMergedRowsInCell = CountMergedRowsInCell + 1
    For X = myRow + 1 To myLastRow
        If ThisWorkbook.Sheets(mySheet).Cells(X, myColumn).Value = "" Then
            CountMergedRowsInCell = CountMergedRowsInCell + 1
        Else
            Exit Function
        End If
    Next
End If
End Function