Copy & insert row based on cell numbers

[littlefish71]

Hi Guys,

I have had some luck but my method had led me in a direction which is making things tricky. So I need some help!!

I have a table which appears below. Column C indicates the serial number of an item which has been checked. Column D indicates the last serial number which is checked in the range.

ie: item 'a' serial numbers 285 to 295 have been checked. What I require is row 2 to be copied and inserted on the next row and automatically index the serial number to 286 until it reaches the last serial number of 295.
ie: the macro inserts 11 rows of all the same data except for column C which indexes on each row. Then moves onto the next row ie: in this table it is row 3 is copied and repeated 4 times indexing 352 to 355 and so on. if there is only 1 serial number then it remains as is.

	A</SPAN>	B</SPAN>	C</SPAN>	D</SPAN>
1</SPAN>	Prod date</SPAN>	Item Name</SPAN>	1st Item No.</SPAN>	Last Item No.</SPAN>	</SPAN>
2</SPAN>	01/01/2013</SPAN>	a</SPAN>	285</SPAN>	288</SPAN>	</SPAN>
3</SPAN>	01/01/2013</SPAN>	b</SPAN>	352</SPAN>	356</SPAN>	</SPAN>
4</SPAN>	01/01/2013</SPAN>	c</SPAN>	343</SPAN>		</SPAN>
5</SPAN>	01/01/2013</SPAN>	d</SPAN>	108</SPAN>		</SPAN>
6</SPAN>	01/01/2013</SPAN>	e</SPAN>	200</SPAN>	208</SPAN>	</SPAN>
7</SPAN>	01/01/2013</SPAN>	f</SPAN>	30</SPAN>		</SPAN>
8</SPAN>	01/01/2013</SPAN>	g</SPAN>	62</SPAN>	63</SPAN>	</SPAN>

<TBODY>
</TBODY>

Part Finished table

1</SPAN>	Prod date</SPAN>	Item Name</SPAN>	1st Item No.</SPAN>	Last Item No.</SPAN>
2</SPAN>	01/01/2013</SPAN>	a</SPAN>	285</SPAN>	288</SPAN>
3</SPAN>	01/01/2013</SPAN>	a</SPAN>	286</SPAN>	288</SPAN>
4</SPAN>	01/01/2013</SPAN>	a</SPAN>	287</SPAN>	288</SPAN>
5</SPAN>	01/01/2013</SPAN>	a</SPAN>	288</SPAN>	288</SPAN>
6</SPAN>	01/01/2013</SPAN>	b</SPAN>	352</SPAN>	356...</SPAN>

<TBODY>
</TBODY>

 

[hiker95]

littlefish71,

Welcome to the MrExcel forum.

Sample raw data:

Excel 2007
	A	B	C	D
1	Prod date	Item Name	1st Item No.	Last Item No.
2	1/1/2013	a	285	288
3	1/1/2013	b	352	356
4	1/1/2013	c	343
5	1/1/2013	d	108
6	1/1/2013	e	200	208
7	1/1/2013	f	30
8	1/1/2013	g	62	63
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sheet1

After the macro:

Excel 2007
	A	B	C	D
1	Prod date	Item Name	1st Item No.	Last Item No.
2	1/1/2013	a	285	288
3	1/1/2013	a	286	288
4	1/1/2013	a	287	288
5	1/1/2013	a	288	288
6	1/1/2013	b	352	356
7	1/1/2013	b	353	356
8	1/1/2013	b	354	356
9	1/1/2013	b	355	356
10	1/1/2013	b	356	356
11	1/1/2013	c	343
12	1/1/2013	d	108
13	1/1/2013	e	200	208
14	1/1/2013	e	201	208
15	1/1/2013	e	202	208
16	1/1/2013	e	203	208
17	1/1/2013	e	204	208
18	1/1/2013	e	205	208
19	1/1/2013	e	206	208
20	1/1/2013	e	207	208
21	1/1/2013	e	208	208
22	1/1/2013	f	30
23	1/1/2013	g	62	63
24	1/1/2013	g	63	63
25
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 03/27/2013
' http://www.mrexcel.com/forum/excel-questions/693794-copy-insert-row-based-cell-numbers.html
Dim r As Long, lr As Long, n As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
  If Cells(r, 4) <> "" Then
    n = Cells(r, 4) - Cells(r, 3)
    If n > 0 Then
      Rows(r + 1).Resize(n).Insert
      Cells(r + 1, 1).Resize(n, 2).Value = Cells(r, 1).Resize(, 2).Value
      With Cells(r + 1, 3).Resize(n)
        .FormulaR1C1 = "=R[-1]C+1"
        .Value = .Value
      End With
      Cells(r + 1, 4).Resize(n).Value = Cells(r, 4).Value
    End If
  End If
Next r
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[littlefish71]

Thanks hiker95, please disregard my second request, I figured it out!!! Thanks again

 

[hiker95]

littlefish71,

You are very welcome. Glad I could help.

Thanks for the feedback, and, come back anytime.