TT

I oversimplified it a little. To suppress the number in column 12 for the first instance of a value, we need to check to make sure it has changed. So, the following should work a little better.

Code:

Sub InsertRowsAtValueChange()
'Update 20140716
Dim Rng As Range
Dim WorkRng As Range
Dim x As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
If WorkRng.Cells(i - 1, 1).Value <> WorkRng.Cells(i, 1).Value Then
WorkRng.Cells(i, 1).EntireRow.Insert
If x <> WorkRng.Cells(i + 1, 1) Then WorkRng.Cells(i + 1, 12).Value = WorkRng.Cells(i + 1, 1)
End If
If x <> WorkRng.Cells(i - 1, 1) Then
WorkRng.Cells(i - 1, 12).Value = WorkRng.Cells(i - 1, 1)
x = WorkRng.Cells(i - 1, 1)
End If
Next
Application.ScreenUpdating = True
End Sub

## Like this thread? Share it with others