Here is a shorter, quicker (it only iterates as many times as there are merged cells) function that will return the same value as your function does, and so you would call it the same way you indicated for calling your function...
Note that my function allows the programmer to pass the argument in as either an actual range object OR the string address for a range. So, using the Set statement from example calling statement, my function could be called either this way...Code:Function FindMergedCells(RangeToSearch As Variant) As Range Dim MergedCell As Range, FirstAddress As String If TypeName(RangeToSearch) = "String" Then Set RangeToSearch = Range(RangeToSearch) Application.FindFormat.MergeCells = True Set MergedCell = RangeToSearch.Find("", LookAt:=xlPart, SearchFormat:=True) If Not MergedCell Is Nothing Then FirstAddress = MergedCell.Address Do If FindMergedCells Is Nothing Then Set FindMergedCells = MergedCell Else Set FindMergedCells = Union(FindMergedCells, MergedCell) End If Set MergedCell = RangeToSearch.Find("", After:=MergedCell, LookAt:=xlPart, SearchFormat:=True) If MergedCell Is Nothing Then Exit Do Loop While FirstAddress <> MergedCell.Address And Not MergedCell Is Nothing End If End Function
Set c = FindMergedCells(Range("J1:N1000"))
or this way...
Set c = FindMergedCells("J1:N1000")




Reply With Quote

Bookmarks