Code To Copy Highlighted Row Insert & Add A, B etc On End Of Data In C

[Dazzawm]

I need a code that when I highlight a row(s) it copies and inserts the row beneath and adds A & B to the end of data in C.

In the sample below if I highlight all three rows the data in C is different in C so I will need a total of 6 rows with A & B on the end in as the result

Excel Workbook
	A	B	C	D	E	F	G
12			MZC1820 2001	data	data	data	data
13			MZC1820 3001	data	data	data	data
14			MZC1820 4001	data	data	data	data
Sheet1

Result

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
12			MZC1820 2001A	data	data	data	data	data	data	data
13			MZC1820 2001B	data	data	data	data	data	data	data
14			MZC1820 3001A	data	data	data	data	data	data	data
15			MZC1820 3001B	data	data	data	data	data	data	data
16			MZC1820 4001A	data	data	data	data	data	data	data
17			MZC1820 4001B	data	data	data	data	data	data	data
Sheet1

Thanks.

 

[John Davis]

Another approach?

Sub Dazzzawm()
Dim i As Long
Dim y As Long
Dim z As Long

y = Selection.Row
z = Selection.Row + Selection.Rows.count - 1
For i = z To y Step -1
    Cells(i, 3).offset(1).EntireRow.Insert
    Rows(i + 1).Value = Rows(i).Value
    Cells(i, 3).offset(1).Value = Cells(i, 3).Value & "B"
    Cells(i, 3).Value = Cells(i, 3).Value & "A"
    Range(Cells(i, 3).offset(, 5), Cells(i, 3).offset(, 7)).Value = Range(Cells(i, 3), Cells(i, 3).offset(, 2)).Value
    Range(Cells(i + 1, 3).offset(, 5), Cells(i + 1, 3).offset(, 7)).Value = Range(Cells(i, 3), Cells(i, 3).offset(, 2)).Value
Next i

End Sub

 

[Dazzawm]

Thanks John, but when I run it I first get a box come up saying 'Excel cannot complete this task with available resources. Choose less data or close applications'. I press ok then a box comes up with a red cross saying '400'?

 

[Worf]

Dazza
Please replace the original dim statement with this new one and test again...

Dim src As Range, t, i&, j&, lr&, chc&, rv

 

[apo]

Yeap.. 50 000 rows is too large for Integer..

From the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> help file:

Integer variables are stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767. Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.

 

[Worf]

Apo

Thanks for adding the explanation mate, I was in a hurry...

 

[Dazzawm]

Thanks Andrew, Worf and John for your help and persistence. Got there in the end!

 

[Dazzawm]

Hi Andrew/Worf. The code works perfect but I need a couple of amendments please, sorry.

1. At the moment I believe the code copies the rows and places them at the bottom of the file then sorts by C. If possible I need the rows inserted directly below and no sort.

2. Because the files I will use this on will always be changing I may need to go back and a row that has a C on the end of the data I may need to copy and insert with a D on the end. Currently it would copy it and put CA, CB and so on...

3. And finally any rows that are copied and inserted I would like coloured sperately to highlight for me, for example like below.

I am sorry this may be a hassle and had I known I would obviously have told you in the beginning.

Excel 2007

	A	B	C	D	E	F	G
1	Header1	Header2	Header3	Header4	Header5	Header6	Header7
2	data	data	MZC1820 2001A	data	data	data	data
3	data	data	MZC1820 2001B	data	data	data	data
4	data	data	MZC1820 2001C	data	data	data	data
5	data	data	MZC1820 3001A	data	data	data	data
6	data	data	MZC1820 3001B	data	data	data	data
7	data	data	MZC1820 3001C	data	data	data	data
8	data	data	MZC1820 4001A	data	data	data	data
9	data	data	MZC1820 4001B	data	data	data	data
10	data	data	MZC1820 4001C	data	data	data	data

<tbody>
</tbody>

Sheet1​

 

[Worf]

Hi Dazza

Are you going to use multiple selections like (c4:c7 AND c11:c15 AND c25) or will the selection always be a single block of cells like c5:c9?

 

[Dazzawm]

It should always be a single block of rows, but the data will sometimes be different in c like the example above.

 

[Dazzawm]

If I selected all the rows below and selected 2 for example the result i would like is shown in second table.

Excel 2010
	A	B	C	D	E	F	G	H	I
12	DATA	DATA	MZC1820 2001	DATA	DATA	DATA	DATA	DATA	DATA
13	DATA	DATA	MZC1820 2001	DATA	DATA	DATA	DATA	DATA	DATA
14	DATA	DATA	MZC1820 3001	DATA	DATA	DATA	DATA	DATA	DATA
15	DATA	DATA	MZC1820 4001	DATA	DATA	DATA	DATA	DATA	DATA
16	DATA	DATA	MZC1820 4001	DATA	DATA	DATA	DATA	DATA	DATA
17	DATA	DATA	MZC1820 4001	DATA	DATA	DATA	DATA	DATA	DATA
Sheet1

After code with highlighted rows.

Excel 2010
	A	B	C	D	E	F	G	H	I
12	DATA	DATA	MZC1820 2001A	DATA	DATA	DATA	DATA	DATA	DATA
13	DATA	DATA	MZC1820 2001A	DATA	DATA	DATA	DATA	DATA	DATA
14	DATA	DATA	MZC1820 2001B	DATA	DATA	DATA	DATA	DATA	DATA
15	DATA	DATA	MZC1820 2001B	DATA	DATA	DATA	DATA	DATA	DATA
16	DATA	DATA	MZC1820 2001C	DATA	DATA	DATA	DATA	DATA	DATA
17	DATA	DATA	MZC1820 2001C	DATA	DATA	DATA	DATA	DATA	DATA
18	DATA	DATA	MZC1820 3001A	DATA	DATA	DATA	DATA	DATA	DATA
19	DATA	DATA	MZC1820 3001B	DATA	DATA	DATA	DATA	DATA	DATA
20	DATA	DATA	MZC1820 3001C	DATA	DATA	DATA	DATA	DATA	DATA
21	DATA	DATA	MZC1820 4001A	DATA	DATA	DATA	DATA	DATA	DATA
22	DATA	DATA	MZC1820 4001A	DATA	DATA	DATA	DATA	DATA	DATA
23	DATA	DATA	MZC1820 4001A	DATA	DATA	DATA	DATA	DATA	DATA
24	DATA	DATA	MZC1820 4001B	DATA	DATA	DATA	DATA	DATA	DATA
25	DATA	DATA	MZC1820 4001B	DATA	DATA	DATA	DATA	DATA	DATA
26	DATA	DATA	MZC1820 4001B	DATA	DATA	DATA	DATA	DATA	DATA
27	DATA	DATA	MZC1820 4001C	DATA	DATA	DATA	DATA	DATA	DATA
28	DATA	DATA	MZC1820 4001C	DATA	DATA	DATA	DATA	DATA	DATA
29	DATA	DATA	MZC1820 4001C	DATA	DATA	DATA	DATA	DATA	DATA
Sheet1

Should I need to go back and highlight these rows and insert

Excel 2010
	A	B	C	D	E	F	G	H	I
27	DATA	DATA	MZC1820 4001C	DATA	DATA	DATA	DATA	DATA	DATA
28	DATA	DATA	MZC1820 4001C	DATA	DATA	DATA	DATA	DATA	DATA
29	DATA	DATA	MZC1820 4001C	DATA	DATA	DATA	DATA	DATA	DATA
Sheet1

Excel 2010
	A	B	C	D	E	F	G	H	I
30	DATA	DATA	MZC1820 4001D	DATA	DATA	DATA	DATA	DATA	DATA
31	DATA	DATA	MZC1820 4001D	DATA	DATA	DATA	DATA	DATA	DATA
32	DATA	DATA	MZC1820 4001D	DATA	DATA	DATA	DATA	DATA	DATA
Sheet1

Thanks.