VBA Error 1004 - Application or Object defined error

Question

Sub MergeCells()  

For i = 0 To 5996 Step 1
     ActiveSheet.Range("G4").Offset(i, 0).Resize(0, 18).Merge 
Next i  

End Sub

Thhis gives Error 1004 - I simply want to merge 18 cells across 5996 rows starting Row 4

GSerg · Accepted Answer · 2018-02-25 13:27:02Z

1

You cannot .Resize(0, 18), the zero is not a valid option.

If you want to keep the existing number of rows while resizing, omit the argument:

.Resize(, 18)

Better yet, remove the loop and merge across in a single command:

Sub MergeCells()
    ActiveSheet.Range("G4").Resize(5997, 18).Merge True
End Sub

answered Feb 25, 2018 at 13:27

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1