Merge Cells with Duplicate Data VBA

Question

I'm trying to get a Macro working to merge cells with duplicate data. It will work on small numbers of cells, but I get the following error if I try to run it on a larger group of cells. I'm not sure if there's a more efficient way for excel to run through this.

Run-Time error '1004': Method 'Range' of object '_Global' failed

Here's the code:

Sub MergeDuplicates()
 Dim varData As Variant, varContent As Variant
 Dim strMyRange As String
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
     strMyRange = ActiveCell.Address
     varContent = ActiveCell.Value
     For Each varData In Selection.Cells
         If varData.Value <> varContent Then
             strMyRange = strMyRange & ":" & Cells(varData.Row - 1, varData.Column).Address & ", " & Cells(varData.Row, varData.Column).Address
             varContent = Cells(varData.Row, varData.Column).Value
         End If
     Next
     strMyRange = strMyRange & Mid(Selection.Address, InStr(1, Selection.Address, ":"), Len(Selection.Address))
     Range(strMyRange).Merge
 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
 End Sub

`Dim varData as Range` and `varContent as String`? Otherwise, what line do you get the error on? — Scott Holtzman, Jan 27 '16 at 14:28
Also, I'd look in to [`avoiding the use of .Select/.ActiveCell`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), which may also cause some issues. — BruceWayne, Jan 27 '16 at 14:55
Merged cells is the greatest evil in Excel and VBA. Don't use it. — Egan Wolf, Jan 27 '16 at 15:16
Scott, I made your changes with no luck. The behavior is the same. It's giving me the error on the line 'Range (strMyRange) .Merge' I believe that the strMyRange variable gets too long. Is there away of putting my merge statement in the For loop and after merging to drop off the set of merged cells? — Cory Anderson, Jan 28 '16 at 14:59

score 0 · Answer 1 · answered Jan 29 '16 at 20:48

I have recreated the issue using the code you posted and it is working for me. I did what you suggested and put the merge into the For loop. Then I split strMyRange using the comma as the delimiter. I set up a test to look for the ":" character in TestArray(0). If it is in that target string, then I know it is ready for the merge. After that I reset strMyRange to the TestArray(1) which is the beginning of the next range.

Note: I was able to step through it with the debugger with 100 cells and it worked. Then I tried running it without any code breakpoints, but it merged all the selected cells. I put a 1 second wait statement right before the final merge and that seems to work.

Here is the code:

 Sub MergeDuplicates()
 Dim varData As Variant, varContent As Variant
 Dim strMyRange As String
 Dim TestArray() As String
 Dim target As String
 Dim pos As Integer




Application.ScreenUpdating = False
Application.DisplayAlerts = False
 strMyRange = ActiveCell.Address
 varContent = ActiveCell.Value
 For Each varData In Selection.Cells
     If varData.Value <> varContent Then
         strMyRange = strMyRange & ":" & Cells(varData.Row - 1, varData.Column).Address & ", " & Cells(varData.Row, varData.Column).Address
         TestArray = Split(strMyRange, ",")
         target = TestArray(0)
         pos = InStr(target, ":")
         If (pos > 0) Then
            Range(target).Merge
            strMyRange = TestArray(1)
         End If
         varContent = Cells(varData.Row, varData.Column).Value
     End If
 Next
 strMyRange = strMyRange & Mid(Selection.Address, InStr(1, Selection.Address, ":"), Len(Selection.Address))
 Application.Wait (Now + #12:00:01 AM#) 'This helps the application run OK if there are no breakpoints.
 Range(strMyRange).Merge
 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
 End Sub

Merge Cells with Duplicate Data VBA

1 Answers1