|
Finding Same Row Value
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. |
Finding Same Row Value
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. |
Finding Same Row Value
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. |
Finding Same Row Value
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. |
Finding Same Row Value
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. |
| All times are GMT +1. The time now is 10:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com