Inserting 2 rows above a certain cell

[Guest]

I have a column with numbers that are sorted in ascending order e.g

1
1
1
3
3
3
7
7
7

The highest number possible is 8. I would like to insert a line above the
spot where the number changes. How can I do this?

 

[Don Guillett]

In this case, one between last 3 and first 7? Or, 3-7 and 1-3?

 

[Bob Phillips]

Subject is at odds with the text!

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow - 1 To 1 Step -1
If .Cells(i, TEST_COLUMN).Value <> .Cells(i + 1,
TEST_COLUMN).Value Then
.Rows(i + 1).Insert
End If
Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I realised the mistake after I posted. Sorry!! Momentary lapse of reason ...

Yes, I did mean insert 2 rows (lines) above the spot where the number
changes i.e. above the first time 3 appears and above the first time 7
appears.

Thank you!
KDJ

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow - 1 To 1 Step -1
If .Cells(i, TEST_COLUMN).Value <> .Cells(i + 1, _
TEST_COLUMN).Value Then
.Rows(i + 1).Resize(2).Insert
End If
Next i

End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)