Hi All,

I have a column that has the following numbers:

1400BAL630
1400CN2400
1400CN2500
1400CN2600
1400CN2630
1400CN2650
1400COC400
1400COC630
1400COC650
1400COC740
1400COC800
1400COC999
1400G3D400
1400G3D650
1400G3D710



I need to test the first 7 characters of the row below. If they are not the
same, I need to insert a row and then start at the next set of 7 characters.

The end result that I am trying to achieve is:

1400BAL630

1400CN2400
1400CN2500
1400CN2600
1400CN2630
1400CN2650

1400COC400
1400COC630
1400COC650
1400COC740
1400COC800
1400COC999

1400G3D400
1400G3D650
1400G3D710


Thanks.

I don't know what the significance of the indented block is, but perhaps:

Sub AddBlankRows()
dim lastrow as long, i as Long
lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 2 step -1
if Left(trim(cells(i,1)),7) < Left(trim(cells(i-1,1)),7) then
rows(i).Insert
end if
Next
end sub


Test on a copy of your data
--
Regards,
Tom Ogilvy

"JN" wrote:

Hi All,

I have a column that has the following numbers:

1400BAL630
1400CN2400
1400CN2500
1400CN2600
1400CN2630
1400CN2650
1400COC400
1400COC630
1400COC650
1400COC740
1400COC800
1400COC999
1400G3D400
1400G3D650
1400G3D710



I need to test the first 7 characters of the row below. If they are not the
same, I need to insert a row and then start at the next set of 7 characters.

The end result that I am trying to achieve is:

1400BAL630

1400CN2400
1400CN2500
1400CN2600
1400CN2630
1400CN2650

1400COC400
1400COC630
1400COC650
1400COC740
1400COC800
1400COC999

1400G3D400
1400G3D650
1400G3D710


Thanks.

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Left(Application.Trim(Replace(Cells(i, "A").Value, Chr(160),
"")), 7) < _
Left(Application.Trim(Replace(Cells(i - 1, "A").Value, Chr(160),
"")), 7) Then
Rows(i).Insert
End If
Next i

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"JN" wrote in message
...
Hi All,

I have a column that has the following numbers:

1400BAL630
1400CN2400
1400CN2500
1400CN2600
1400CN2630
1400CN2650
1400COC400
1400COC630
1400COC650
1400COC740
1400COC800
1400COC999
1400G3D400
1400G3D650
1400G3D710



I need to test the first 7 characters of the row below. If they are not
the
same, I need to insert a row and then start at the next set of 7
characters.

The end result that I am trying to achieve is:

1400BAL630

1400CN2400
1400CN2500
1400CN2600
1400CN2630
1400CN2650

1400COC400
1400COC630
1400COC650
1400COC740
1400COC800
1400COC999

1400G3D400
1400G3D650
1400G3D710


Thanks.

Thanks Tom and Bob,

It worked like a charm.

Would it be possible if you can add a sum formula in the inserted row for
the set of matching column values?


"Bob Phillips" wrote in message
...
Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Left(Application.Trim(Replace(Cells(i, "A").Value, Chr(160),
"")), 7) < _
Left(Application.Trim(Replace(Cells(i - 1, "A").Value, Chr(160),
"")), 7) Then
Rows(i).Insert
End If
Next i

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"JN" wrote in message
...
Hi All,

I have a column that has the following numbers:

1400BAL630
1400CN2400
1400CN2500
1400CN2600
1400CN2630
1400CN2650
1400COC400
1400COC630
1400COC650
1400COC740
1400COC800
1400COC999
1400G3D400
1400G3D650
1400G3D710



I need to test the first 7 characters of the row below. If they are not
the
same, I need to insert a row and then start at the next set of 7
characters.

The end result that I am trying to achieve is:

1400BAL630

1400CN2400
1400CN2500
1400CN2600
1400CN2630
1400CN2650

1400COC400
1400COC630
1400COC650
1400COC740
1400COC800
1400COC999

1400G3D400
1400G3D650
1400G3D710


Thanks.

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim iStart As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Left(Application.Trim(Replace(Cells(i, "A").Value, Chr(160),
"")), 7) < _
Left(Application.Trim(Replace(Cells(i - 1, "A").Value, Chr(160),
"")), 7) Then
Rows(i).Insert
End If
Next i

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iStart = 1
For i = 1 To iLastRow + 1
If Cells(i, "A").Value = "" Then
Cells(i, "A").Value = "=COUNTA(A" & iStart & ":A" & i - 1 & ")"
iStart = i + 1
End If
Next i

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"JN" wrote in message
...
Thanks Tom and Bob,

It worked like a charm.

Would it be possible if you can add a sum formula in the inserted row for
the set of matching column values?


"Bob Phillips" wrote in message
...
Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Left(Application.Trim(Replace(Cells(i, "A").Value, Chr(160),
"")), 7) < _
Left(Application.Trim(Replace(Cells(i - 1, "A").Value,
Chr(160),
"")), 7) Then
Rows(i).Insert
End If
Next i

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"JN" wrote in message
...
Hi All,

I have a column that has the following numbers:

1400BAL630
1400CN2400
1400CN2500
1400CN2600
1400CN2630
1400CN2650
1400COC400
1400COC630
1400COC650
1400COC740
1400COC800
1400COC999
1400G3D400
1400G3D650
1400G3D710



I need to test the first 7 characters of the row below. If they are not
the
same, I need to insert a row and then start at the next set of 7
characters.

The end result that I am trying to achieve is:

1400BAL630

1400CN2400
1400CN2500
1400CN2600
1400CN2630
1400CN2650

1400COC400
1400COC630
1400COC650
1400COC740
1400COC800
1400COC999

1400G3D400
1400G3D650
1400G3D710


Thanks.