Merge Cells VBA Run Time Error 1004

jap7675

New Member
Joined
Nov 13, 2015
Messages
23
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
You need to be on the sheet first. So add this line before your "For i = 3 to 9" line:
Code:
Sheets("Summary").Select
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Try this.
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
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:
Code:
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

New Member
Joined
Nov 13, 2015
Messages
23
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
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!
 

Forum statistics

Threads
1,081,574
Messages
5,359,704
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top