Results 1 to 5 of 5

Thread: Find Merged Cells VBA

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10

    Lightbulb Find Merged Cells VBA

    Hi All,

    Here is a function to find merged cells. Hope this would find useful

    Code:
    Function FindMergedCells(ByRef RangeToSearch As Range) As Range
            
        'Krishnakumar @ ExcelFox.com
        Dim dic     As Object
        Dim r       As Long
        Dim c       As Long
        Dim k, i    As Long
        Dim UB1     As Long
        Dim UB2     As Long
        
        UB1 = RangeToSearch.Rows.Count
        UB2 = RangeToSearch.Columns.Count
        
        Set dic = CreateObject("scripting.dictionary")
        
        For r = 1 To UB1
            For c = 1 To UB2
                If RangeToSearch.Cells(r, c).MergeArea.Cells.Count > 1 Then
                    dic.Item(RangeToSearch.Cells(r, c).MergeArea.Cells.Address(0, 0)) = Empty
                End If
            Next
        Next
        
        If dic.Count Then
            k = dic.keys
            For i = LBound(k) To UBound(k)
                If FindMergedCells Is Nothing Then
                    Set FindMergedCells = RangeToSearch.Range(CStr(k(i)))
                Else
                    Set FindMergedCells = Union(FindMergedCells, RangeToSearch.Range(CStr(k(i))))
                End If
            Next
        End If
        
    End Function

    and call the function like..

    Code:
    Sub kTest()
        
        Dim c As Range
        
        Set c = FindMergedCells(Range("j1:n1000"))
        If Not c Is Nothing Then c.Interior.Color = 65535
    
    End Sub
    Enjoy !!
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    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...

    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
    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...

    Set c = FindMergedCells(Range("J1:N1000"))

    or this way...

    Set c = FindMergedCells("J1:N1000")

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi Rick,

    I thought about the findformat method. But I think findformat was introduced in XL 2002/2003 (not sure which one).

    So I thought it would be better a function whch works on all versions(not sure about XL 97 though)

    Another point, if you pass a string it must be a range address, otherwise it would throw error.

    I did a small test on A1:K10000 with 1169 merged areas, and the average time taken is as follows:

    Ricks - 5 secs
    Mine - 7 secs

    Anyway thanks for posting an alternative (I would say native) method.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    I thought about the findformat method. But I think findformat was introduced in XL 2002/2003 (not sure which one).

    So I thought it would be better a function whch works on all versions(not sure about XL 97 though)
    I only have XL2003 and XL2007 installed, plus I only came to Excel maybe 5 years ago, so I am unaware of any limitation on versions of Excel prior to XL2003. Besides, why worry about earlier versions of Excle... everyone has moved up to at least XL2003 by now, right?

    Quote Originally Posted by Admin View Post
    Another point, if you pass a string it must be a range address, otherwise it would throw error.
    Isn't that what functions normally do when they get passed invalid arguments? I would think it is up to the programmer to either filter out improper argument values before the function is called or to handle errors generated by the function after they are produced. Did you have something different in mind for what my function should do if the passed in String value was not a range address?

    Quote Originally Posted by Admin View Post
    I did a small test on A1:K10000 with 1169 merged areas, and the average time taken is as follows:

    Ricks - 5 secs
    Mine - 7 secs
    While I would always expect my function to be faster than what you posted, the percentage difference between them is highly dependent on the number of merged cells involved. Both of our routines repeatedly use the Union function for each merged cell in the range. You test with 1169 merged cells means 1169 Union operations... the Union method gets progressively slower with each new member added to it and I think 1169 of them is enough to slow things down noticeably and, as a result, hide the true difference between our codes. What times do you get if you repeat that test with, say, 5 merged areas?
    Last edited by Rick Rothstein; 02-25-2012 at 07:12 AM.

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    I know people who still use XL 2000

    I have no doubt that your function is faster as it only loops only on merged areas where as mine loops on every cells. But as I said, I wrote this function keeping in mind the version compitability.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Trim all Cells in a Worksheet - VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 6
    Last Post: 08-21-2015, 08:22 AM
  2. Replies: 13
    Last Post: 06-10-2013, 09:05 AM
  3. VBA - Find Last End Value
    By ivandgreat in forum Excel Help
    Replies: 3
    Last Post: 05-02-2013, 10:37 AM
  4. Vba Code to find value and paste on certain row
    By jwitte in forum Excel Help
    Replies: 3
    Last Post: 11-28-2012, 08:52 PM
  5. Find All Cells That Contain A Certain Value
    By Rasm in forum Excel Help
    Replies: 3
    Last Post: 04-26-2011, 02:43 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •