Merge Cells VBA Run Time Error 1004

[jap7675]

Hello,

I'm having a small problem with part of a code - and just dont know why it isn't working when using Private Sub Workbook_Open() whereas it works when using commandbutton()!

Private Sub Execute()

'Clear All Previous Data
With Sheets("Summary").Range("A3:K310")
.Interior.ColorIndex = none
.ClearContents
.MergeCells = False
End With

'Speeds up calculation by stopping certain Excel Actions
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.EnableEvents = False

'Recalculates Background
Worksheets("Plant_Bkgrd").Calculate

'TITLE & SUMMARY SETTINGS------------------------------------------------------------------------------------

'Title: Display Settings
Dim i As Integer 'Merge first columns
For i = 3 To 9
Sheets("Summary").Range(Cells(i, 2), Cells(i, 9)).MergeCells = True
Next i​

the problem flags up on the penultimate line, and i just cant see a reason.

Please help!

All the best

 

[Joe4]

You need to be on the sheet first. So add this line before your "For i = 3 to 9" line:

Sheets("Summary").Select

 

[Norie]

Try this.

Dim i As Integer 'Merge first columns
For i = 3 To 9
    With Sheets("Summary")
        .Range(.Cells(i, 2), .Cells(i, 9)).MergeCells = True
    End With
Next i

PS You should avoid using merged cells.

 

[Joe4]

PS You should avoid using merged cells.

Norie makes a good point. Merged cells can cause lots of issues for things like sorting and VBA.
If you are just merging across a row, you can get the same visual effects using the "Center Across Selection" formatting option without all the issues that Merged Cells bring to the table.

That code would look like this:

Dim i As Integer
For i = 3 To 9
    With Sheets("Summary")
        .Range(.Cells(i, 2), .Cells(i, 9)).HorizontalAlignment = xlCenterAcrossSelection
    End With
Next i

 

[jap7675]

Perfect,

Thankyou for all your help - much appreciated!
Thanks also for the pointer - I'm aware I shouldnt use merged cells, but theres no other way to get my desired look!

 

[Joe4]

I'm aware I shouldnt use merged cells, but theres no other way to get my desired look!

Did you see what I said up above? If you are just merging across single rows at a time (which it sounds like what you are doing), the "Center Across Selection" should give you the desired look you want without all the issues!