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

 

[dmt32]

Hi,
you do not say which error is being displayed but will hazard a guess at Application-Defined or Object-Defined Error?
If this is so, check & see if the worksheet code applies to is protected & if so, unprotect it & re-try your code.

Dave

 

[Saurabhj]

Hi, I tried the code given and it's working fine. So seems issue in the sheet.

Please confirm the line on which error is generated.

 

[Luke777]

Hi, I tried the code given and it's working fine. So seems issue in the sheet.

Please confirm the line on which error is generated.
View attachment 53518

it would be line 9 as you have it I believe "Range(cell, cell.offset(1,0)).merge"

What the heck could be making it a sheet issue?

 

[Saurabhj]

Hi Luke777, As per Dave's comment please check that sheet is not protected.

 

[Luke777]

Hi Luke777, As per Dave's comment please check that sheet is not protected.

View attachment 53519

Nope, I can't see as there are any forms of sheet protection active - I have the option to protect, but there's nothing to unprotect

 

[Fluff]

Firstly I would recommend against using merged cells, they are an abomination & should be avoided like the plague. They will only cause your grief & problems.
That said, is your data in a structured table?

 

[Luke777]

Firstly I would recommend against using merged cells, they are an abomination & should be avoided like the plague. They will only cause your grief & problems.
That said, is your data in a structured table?

It's a report generated by 3rd party software/site - so its just dumped onto a sheet with such useful headers as "column 1" and "column 2" followed by actual data from row 2 down. There's no special tables involved as far as I can tell - it looks like it could have been entered manually. Though just incase I am being a bit dense, how would I check?

For full disclosure, in the current report A2:A12 contains the same information (a repeated reference number), B2:B12 contains the same information (a repeated string), C2:C12 contains the same information (another repeated string) - after that D:N contain the actually useful information. My plan was to merge the repeated information so i end up with useful reference points for running some sort of offset later on.

For example, if I wanted to look at the last entry of D for "916" in Column A, I could do a match offset formula or whatever. I figured merging might make that easier.

Regardless, sure would be nice to know what's going wrong at the moment

 

[Fluff]

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"

 

[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

Yes, I do... that's a little embarrassing lol