Annoying run-time error '1004'

[Luke777]

Hi all,

I've looked at a few posts regarding this error and the solutions don't appear to be working.

I've tried two bits of code for merging cells in column A. A contains sorted values e.g. A1:A5 all contain '916', A5:A13 contain the word chicken etc etc. I am simply trying to merge these repeats.

I've tried the code given in THIS YouTube video

as well as the below found HERE

Sub MergeSimilarCells()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set myRange = Range("A1:A6")
 CheckAgain:
    For Each cell In myRange
        If cell.Value = cell.Offset(1, 0).Value And Not IsEmpty(cell) Then
            Range(cell, cell.Offset(1, 0)).Merge
            cell.VerticalAlignment = xlCenter
            GoTo CheckAgain
        End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True 
End Sub

For testing, I have changed absolutely nothing - though extended the range makes no difference to the error I get.

I've also tried specifying the worksheet in use (though there is only one open to begin with) which didn't help.

The Exact error is "Run-time error '1004': Application-defined or object-defined error" - which debug highlights the line with ".merge"

Though I have notice that hovering over the first cell of "range(cell, cell.Offset..." shows cell=916... I'm not sure, but is it looking at the number in those cells and thinking that is the cell reference rather than treating it as a string?

Thanks al

 

[Fluff]

In that case that's the reason. You cannot have merged cells in a table.

 

[Luke777]

Try to match data when you have merged cells, is (IMO) harder than doing it without & usually involves the use of volatiles functions.

If you select a cell in your col A range & look at the ribbon, do you see an extra tab called "Table Design"
View attachment 53521

Do I need to reference the table differently to just a standard range?

 

[Fluff]

In what way?

 

[Luke777]

In what way?

sorry, I missed the reply before this somehow.. disregard! and thanks for your assistance!

 

[Fluff]

Glad to help & thanks for the feedback.