VBA/Macro to Duplicate Rows

[StarExcel]

Hi,

I have been searching for several days now and have been unable to find a VBA coding for the following, does anyone have the answer to this or is it even possible?:

Example:
I have a spreadsheet that has data in columns A to G and what I need to do is duplicate a row twice to the bottom of the data where the value of column E equals X then I need a formula attached to duplicate row 1 as the value of column G times 92%
and then for duplicate row 2 as the value of Column G times 8% then I would like to delete the original entry.

Thanks,
SH

[gjcase]

Let me see if I understand:
You have data in several rows (assume 10 for this example) covering columns A:G.

If E1 = X then you want A1:G1 copied to A11:G11 and each value A11:G11 multiplied by .92 * G11, AND A1:G1 copied to A12:G12 and multiplied by .08 * G12 Then Delete row 1 (remove the entire row, not just the values); If E1<>X then skip this row. Then repeat for the remaining rows (either rows 2-10 or 1-9 if deleted row 1)?

[StarExcel]

Hi gjcase,

Yes that is exactly what I am looking for, the below data maybe explains it more clearly.

Original Data:

2 0 1 20 4200000 UK 5000
2 0 1 20 4210000 UK 4000
2 0 1 20 4220000 UK 3000
2 0 1 20 4230000 UK 2000
2 0 1 20 4240000 UK 100

Result (Assuming That 420000 code is the one code needing duplicates)

2 0 1 20 4210000 UK 4000
2 0 1 20 4220000 UK 3000
2 0 1 20 4230000 UK 2000
2 0 1 20 4240000 UK 100
2 0 1 20 4200000 UK 4600
2 0 1 20 4200000 UK 400

Thanks,
SH

[gjcase]

Try this:

Please Login or Register  to view this content.

This does the calculations in matrices, then deletes the original range & overwrites with the Results matrix. It requires that you select the original data first.

[gjcase]

After reading your post wiith the data, this does the job a bit better:

Please Login or Register  to view this content.

Note however, that it does not put the new rows at the end; rather it substitutes for the original in place. If you want it at the end, we can do that as well.

If X may be something other than a number, change "Dim X as Double" to "Dim X as Variant"