Getting Error in Micro(for Loop)

[Damian]

I am trying to add extra rows and the merge certatin columns together, but I
am getting a

Run-Time error '1004'
Application-defined or object-defined error

How come?

Here is the code:
Sub AddExtraRows()

ActiveSheet.Unprotect Password:="eli"
Rows("32:80").Insert Shift:=xlDown

For i = 32 To 103
Range(Cells(i, 3), Cells(i, 6)).Merge
Next i

For j = 32 To 103
Range(Cells(j, 7), Cells(j, 12)).Merge
Next j

For k = 32 To 103
Range(Cells(k, 13), Cells(k, 15)).Merge
Next k

With ActiveSheet
.Protect Password:="eli"
.EnableSelection = xlUnlockedCells
End With
End Sub

 

[Jim Thomlinson]

That exact code works for me. It can be tightened up as follows but there is
nothing wrong with it...

Sub AddExtraRows()

With ActiveSheet
.Unprotect Password:="eli"
.Rows("32:80").Insert Shift:=xlDown

For i = 32 To 103
.Range(.Cells(i, 3), .Cells(i, 6)).Merge
.Range(.Cells(i, 7), .Cells(i, 12)).Merge
.Range(.Cells(i, 13), .Cells(i, 15)).Merge
Next i

.Protect Password:="eli"
.EnableSelection = xlUnlockedCells
End With
End Sub

One question I would have since you are merging cells do you have existing
merged cells prior to running the code? Macros and merged cells don't get
along...

 

[Dave Peterson]

..merge has a parm (Across) that you can use to merge the rows in the range. If
you use that, you don't have to loop through the rows.



Option Explicit
Sub AddExtraRows()

Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.Unprotect Password:="eli"
.Rows("32:80").Insert
.Range("C32:C103").Resize(, 4).Merge across:=True
.Range("G32:G103").Resize(, 6).Merge across:=True
.Range("M32:M103").Resize(, 3).Merge across:=True
.Protect Password:="eli"
.EnableSelection = xlUnlockedCells
End With
End Sub

 

[Damian]

Thank You for the Improved code. Greately Appriciated. It works perfectly now.
(The reson why mine did not work is because after creating the rows the
sheet protected itself again so I had to unprotect it again before merging)

To answare your question about merge cells; Yes the worksheet has other
merge cells BUT the ones I am merging are not merged so It works.

Thank You guys.

 

[Damian]

Thank You.

..merge has a parm (Across) that you can use to merge the rows in the range. If
you use that, you don't have to loop through the rows.



Option Explicit
Sub AddExtraRows()

Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.Unprotect Password:="eli"
.Rows("32:80").Insert
.Range("C32:C103").Resize(, 4).Merge across:=True
.Range("G32:G103").Resize(, 6).Merge across:=True
.Range("M32:M103").Resize(, 3).Merge across:=True
.Protect Password:="eli"
.EnableSelection = xlUnlockedCells
End With
End Sub

 

[Jim Thomlinson]

I just keep learning new stuff. I had no idea that merge had that parameter.
That could be because I never merge cells so I guess I will be hard pressed
to use this but it is still good to know. Thanks Dave...

 

[Dave Peterson]

In general, I hate merged cells, too. But every so often, they're the only way
to make the worksheet look pretty. But they cause so much trouble, I do my best
to live with the non-pretty sheets <vbg>.

If you've modified your toolbar (xl2003 menu stuff), you can actually add an
icon to do this "merge across" for the selected range(s).

Tools|Customize|Commands tab|format category
About half way down, there's that "Merge Across" icon
drag it to your favorite toolbar.