Condition to duplicate row of data

georgecheng

Board Regular
Joined
Feb 27, 2008
Messages
57
Dear bro and sister, I need your advice on the VBA script to duplicate the data when existing condition met.
In Column B, if there a "(SP)"" exist, I need the row to duplicate 3 more set of them. Example as below.
EXAMPLE
NoCodeWeightItemCodedatePostageRemark
99263435,AA5.7643187943812/1/20151.7A
99263744,RJ x 1 6.8342361047512/1/20150.6B
99264668,Bag, (SP)4.9543852753612/1/20151.15C
99270401,BagA4.9543852753612/1/20150D
99271857,PW17 1.3541244150512/1/20150E
99274062,LG28 8.8942975274412/1/20151.7F
99276635,LG20,V16, (SP)9.3242975274412/1/20150.6G
99277063,LG20,V16 9.3242975274412/1/20150.6H
99278220,LG20,V16,BA-BBm, CD14.2742975274412/1/20150.6I
RESULT
NoCodeWeightItemCodedatePostageRemark
99263435,AA5.7643187943812/1/20151.7A
99263744,RJ x 1 6.8342361047512/1/20150.6B
99264668,Bag, (SP)4.9543852753612/1/20151.15C
9.9E+07,Bag, (SP)4.9543852753612/1/20151.15C
9.9E+07,Bag, (SP)4.9543852753612/1/20151.15C
9.9E+07
,Bag, (SP)4.9543852753612/1/20151.15C
99270401,BagA4.9543852753612/1/20150D
99271857,PW17 1.3541244150512/1/20150E
99274062,LG28 8.8942975274412/1/20151.7F
99276635,LG20,V16, (SP)
9.3242975274412/1/20150.6G
9.9E+07,LG20,V16, (SP)9.3242975274412/1/20150.6G
9.9E+07,LG20,V16, (SP)9.3242975274412/1/20150.6G
9.9E+07,LG20,V16, (SP)9.3242975274412/1/20150.6G
99277063,LG20,V16 9.3242975274412/1/20150.6H
99278220,LG20,V16,BA-BBm, CD14.2742975274412/1/20150.6I

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

georgecheng

Board Regular
Joined
Feb 27, 2008
Messages
57
If the script can't be insert to row in-between, added to the last of the existing row will also be good. After that, i can use re-sort data for it. Thanks
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG20Feb57
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("B" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = Lst To 1 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]With[/COLOR] Range("B" & n)
            [COLOR="Navy"]If[/COLOR] InStr(.Value, "(SP)") > 0 [COLOR="Navy"]Then[/COLOR]
                .EntireRow.Copy
                .EntireRow.Offset(1).Resize(3).Insert
            [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,884
Members
414,029
Latest member
mrwilker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top