Problem with code when inserting row at worksheet

[celiel]

Hello all.

I have the following code. In some cells in the worksheet there is data that is separated with "," for example "kkkkk,ddddd" and I want to separate the data where the "," is found and put each string found to the cells bellow, and copying the remaining row.

The code works fine in the test worksheet. The only change that i did was to change the name of the worksheet. When I run it i always get runtime error 1004 Application-defined or object-defined error when i am trying to insert a new row.

I cant figure out the problem. The only thing that I think it can cause problem is that the button is in a different worksheet than the code is referring to.

Private Sub CommandButton5_Click()
Dim str As String
Dim strarr As Variant
Dim i As Long
Dim rng As Range
Worksheets("Total").Unprotect
For i = 2 To Worksheets("Total").Rows.count
 str = Worksheets("Total").Cells(i, 6)
 If InStr(1, str, ",") > 0 Then
 Set rng = Worksheets("Total").Cells(i, 6).EntireRow
  strarr = Split(str, ",")
  Worksheets("Total").Range(Cells(i + 1, 6), Cells(i + UBound(strarr), 6)).EntireRow.Insert (xlShiftDown)
  For j = 0 To UBound(strarr)
   Worksheets("Total").Cells(j + i, 6).EntireRow.Value = rng.Value
   Worksheets("Total").Rows(j + i).Interior.Color = vbRed
   Worksheets("Total").Cells(j + i, 6) = strarr(j)
  Next j
 End If
 Next i
End Sub

[royUK]

This wrks for me, but it's really slow.

Private Sub CommandButton5_Click()
Dim str As String
Dim strarr As Variant
Dim i As Long
Dim rng As Range
With Worksheets("Total")
.Unprotect
For i = 2 To .Rows.Count
 str = .Cells(i, 6)
 If InStr(1, str, ",") > 0 Then
 Set rng = .Cells(i, 6).EntireRow
  strarr = Split(str, ",")
  .Range(Cells(i + 1, 6), Cells(i + UBound(strarr), 6)).EntireRow.Insert (xlShiftDown)
  For j = 0 To UBound(strarr)
   .Cells(j + i, 6).EntireRow.Value = rng.Value
   .Rows(j + i).Interior.Color = vbRed
   .Cells(j + i, 6) = strarr(j)
  Next j
 End If
 Next i
 End With

End Sub

A dummy workbook would help, I'm sure you are checking too many cells

[celiel]

Thanks a lot for your help.

Ok something peculiar. When I created a new button to the worksheet with the data, it run fine.
When i run it from a different worksheet i always get the error.

I have also tried to run the code at excel 2010 and the button created at the worksheet with the data and i got the 1004 error -- insert method of range class failed.

The worksheet with the data has about 20,000 rows filled with data. Is there a limitation to the insert method ?

I can't figure out the problem except that I have too many cells.

Tomorrow I will try to post a dummy worksheet, because i am not at work.

Thanks in advance.