Hi Howard,

Am Sat, 6 Dec 2014 09:54:12 -0800 (PST) schrieb L. Howard:

.Formula = "=SUM(" & myRng1 & ")": .Value = .Value

I would prefer formulas. But if the user want values try in in one step:

Sub InsertRows()

Dim LRow As Long, Start As Long, i As Long

Dim wsh As Worksheet

Dim n As Long, lRowsInsert As Long, CountUniques As Long

Dim myRng1 As String, myrng2 As String

Const myRow As Long = 10

Const myCol As Long = 3

lRowsInsert = Application.InputBox("Enter number of rows to insert", _

"Insert rows", Type:=1)

If lRowsInsert = 0 Or lRowsInsert = False Then Exit Sub

If lRowsInsert < 1 Then

MsgBox "Error - please enter a value equal to or greater than 1"

Exit Sub

End If

MsgBox "This code will insert " & lRowsInsert & " row/s" & vbCr & _

"wherever CLASS values change in" & vbCr & "column " _

& myCol & " starting from row number " & myRow

Application.ScreenUpdating = False

Set wsh = Sheets("5. Tracker")

With wsh

LRow = .Cells(Rows.Count, myCol).End(xlUp).Row

CountUniques = Evaluate("=Countif(" & .Range(.Cells(myRow, myCol),

..Cells(LRow, myCol)).Address & _

"," & .Range(.Cells(myRow, myCol), .Cells(LRow, myCol)).Address &

")")

For i = LRow To myRow + 1 Step -1

If .Cells(i - 1, myCol) <> .Cells(i, myCol) And _

Len(.Cells(i - 1, myCol)) > 0 Then

Rows(i).Resize(rowsize:=lRowsInsert).Insert

i = i - lRowsInsert

End If

Next

Start = myRow

LRow = .Cells(Rows.Count, myCol).End(xlUp).Row

For i = Start To LRow + lRowsInsert

If Len(.Cells(i, 28)) = 0 And Not .Cells(i - 1, 28).HasFormula

Then

For n = 28 To 89

If n Mod 5 = 3 Or n Mod 5 = 0 Then

myRng1 = .Range(.Cells(Start, n), .Cells(i - 1,

n)).Address

With .Cells(i, n)

.Formula = "=SUM(" & myRng1 & ")"

.Interior.ColorIndex = 17

.Borders(xlEdgeTop).Weight = xlMedium

.Borders(xlEdgeBottom).Weight = xlMedium

End With

ElseIf n Mod 5 = 4 Or n Mod 5 = 1 Then

myRng1 = .Range(.Cells(Start, n), .Cells(i - 1,

n)).Address

myrng2 = .Range(.Cells(Start, n - 1), .Cells(i - 1, n -

1)).Address

With .Cells(i, n)

.Formula = "=SUMPRODUCT(" & myRng1 & "," & myrng2 &

")"

.Interior.ColorIndex = 17

.Borders(xlEdgeTop).Weight = xlMedium

.Borders(xlEdgeBottom).Weight = xlMedium

.NumberFormat = "[$$-409]#,##0.00"

End With

End If

Next

Start = i + lRowsInsert

i = i + lRowsInsert

End If

Next

With .Range(.Cells(myRow, "AB"), .Cells(LRow,

"CK")).SpecialCells(xlCellTypeFormulas)

.Value = .Value

End With

End With

Application.ScreenUpdating = True

End Sub

Regards

Claus B.