Method 'MergeArea' of object 'Range' failed

Question

I'll preface this post by saying I have implemented similar logic elsewhere in my workbook without issue, and in my research I have not encountered anyone citing the same difficulty.

In my sheet, I am using a 2-cell merged "plus button" that when a user clicks on it will insert a row above where the plus button is located. (Again I am using numerous buttons like this in my worksheet). The button will shift up and down the sheet but it will always remain in column A. There is also the potential for there to be numerous plus buttons in column A (hence why I chose not to use .Find).

Initially I tried to base my logic on whether the cell clicked on had a value of "+". This didn't work and I figured it was because the "Target" contains two cells and only one has the "+". So because the only merged cells in that column will be the plus icon, I made it evaluate on cell count instead. This works, but then when it inserts rows, it inserts two rows because Target is two cells. So my workaround was to unmerge Target, insert row, then re-merge. (I also do similar things elsewhere in my workbook).

However I am getting the error message 1004: "Method 'MergeArea' of object 'Range' failed" on the indicated line below. Target is an object and the cells are merged so I don't know why this method would fail.

If Not Intersect(Target, Range("A17:A1000")) Is Nothing Then
    If Target.Cells.Count = 2 Then
       With Target
            If .MergeCells Then '<- highlighted code on error
                .MergeArea.UnMerge
                'code to insert, re-merge, etc
            End If

       End With
    End If
End If

If you're problem is inserting a row using a merge cell, will Target.EntireRow.Insert xlDown work? — L42
@L42 The problem isn't inserting a row, the problem is that it's inserting two rows and I only need one. The only way around this I can think of is by unmerging the merged cell first. — ipenguin67

ipenguin67 ipenguin67 · Accepted Answer · 2017-10-02T19:44:23

Although .MergeArea.UnMerge is verbatim what the MSDN page says to do for this scenario, I tried it without the .MergeArea and it worked. So my code is now

   With Target
        If .MergeCells Then '<- highlighted code on error
            .UnMerge
            'code to insert, re-merge, etc
        End If

   End With

And that appears to work fine, so problem solved!

Method 'MergeArea' of object 'Range' failed

1 Answers