PDA

View Full Version : Insert Row based on partial cell value



Hankins
03-18-2007, 07:03 PM
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
03-18-2007, 08:35 PM
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

lucas
03-18-2007, 09:43 PM
another clean solution....nice one Anne