Excel VBA Function to find and give me the row number of the first merged cell in a column

[maverick93]

I am in need of a function to find and give me the row number of the first merged cell in a column.

Right now I have a list A1:C100 and none of those cells are merged accept one row Merge cell. I need a function that will locate this merged cell and return a row number.


so if the the merged cell is in A20:C20 I need the function to return the row number which would be 20.

Thanks

 

[Peter_SSs]

Something like this?
Return of 0 means no merged rows

Sub Test()
  Dim FirstMergeRow As Long
  
  FirstMergeRow = Merge_Row(Range("A1:A100"))
  MsgBox FirstMergeRow
End Sub

Function Merge_Row(rng As Range) As Long
   Application.FindFormat.Clear
   Application.FindFormat.MergeCells = True
   On Error Resume Next
   Merge_Row = rng.Find(What:="", After:=rng.Cells(rng.Cells.Count), SearchDirection:=xlPrevious, SearchFormat:=True).Row
   On Error GoTo 0
   Application.FindFormat.Clear
End Function

 

[maverick93]

Hello Peter_SSs,

Thank you so much for your help. Unfortunately the code doesn't quite work the way that I need. It is actually giving me the row number of the last merged cell and not the first.

I figured this would be a tough one so thanks for the try!

 

[Peter_SSs]

It is actually giving me the row number of the last merged cell and not the first.

I figured this would be a tough one so thanks for the try!

No, it isn't tough (I don't think) - I just made an error. Try this change to the line in question.

Merge_Row = rng.Find(What:="", After:=rng.Cells(rng.Cells.Count), SearchDirection:=xlNext, SearchFormat:=True).Row