Insert Row based on partial cell value

[Hankins]

How can you insert rows in a column based on a partial cell value.

For example, I have several cost codes that I'd like to insert a row between departments. The third digit in the number indicates the department.

10101
10105
10103
10300 << (Insert Row Here)
10308
10309
10960 << (Insert Row Here)
10990

Thanks for your help.

[geekgirlau]

[vba]Sub SplitCodes()
Dim rng As Range
Dim strDept As String


strDept = Mid(Range("A1").Value, 3, 1)

' work through the used range in a single column
' (change the starting point as required)
For Each rng In Range(Range("A1"), Range("A1").End(xlDown))
If Mid(rng.Value, 3, 1) <> strDept Then
strDept = Mid(rng.Value, 3, 1)
rng.EntireRow.Insert
End If
Next rng
End Sub[/vba]

[lucas]

another clean solution....nice one Anne