Insert # of Rows based on Count

[Eskypades]

I'm once again turning to the wizards of the MrExcel forums in hopes of enlightenment.

I have a spreadsheet with two tabs. The first tab (IMPORT) has a list of SKU numbers in column E. For example:

COLUMN E
sku
631
633
1095
1098


The second tab (SIZES) also has a column (A) for skus along with a size-id in column B. For example:


<table style="width: 128px; height: 179px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;width:48pt" height="20" width="64">COL A
sku</td> <td class="xl67" style="border-left:none;width:48pt" width="64">COL B
size_id</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;width:48pt" height="20" width="64">631</td> <td class="xl68" style="border-left:none;width:48pt" width="64">2441</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">633</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">2460</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">1095</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">4926</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">1095</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">19310</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">1095</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">4927</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">1095</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">4928</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">1098</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">4944</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">1098</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">4942</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none; width:48pt" height="20" width="64">1098</td> <td class="xl68" style="border-top:none;border-left:none;width:48pt" width="64">4943</td></tr></tbody></table>

I need to look up the SKU number in column E of the IMPORT tab, count how many times that SKU appears in column A of the SIZES tab, and insert that many rows above the original SKU entry of the IMPORT tab. The end result would look like this:
<table border="0" cellpadding="0" cellspacing="0" width="255"><tbody><tr height="20"></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt;width:48pt" height="20" width="64">
</td><td class="xl67" style="width:48pt" width="64">sku</td><td style="width:95pt" width="127">[notes]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 2</td><td class="xl68" style="width:48pt" width="64">
</td><td>[1 row for 631]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 3</td><td class="xl68" style="border-top:none;width:48pt" width="64">631</td><td>[original row]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 4</td><td class="xl68" style="border-top:none;width:48pt" width="64">
</td><td>[1 row for 633]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 5</td><td class="xl68" style="border-top:none;width:48pt" width="64">633</td><td>[original row]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 6</td><td class="xl68" style="border-top:none;width:48pt" width="64">
</td><td>[4 rows for 1095]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 7</td><td class="xl68" style="border-top:none;width:48pt" width="64">
</td><td>[4 rows for 1095]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 8</td><td class="xl68" style="border-top:none;width:48pt" width="64">
</td><td>[4 rows for 1095]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 9</td><td class="xl68" style="border-top:none;width:48pt" width="64">
</td><td>[4 rows for 1095]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 10</td><td class="xl68" style="border-top:none;width:48pt" width="64">1095</td><td>[original row]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 11</td><td class="xl68" style="border-top:none;width:48pt" width="64">
</td><td>[3 rows for 1098]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 12</td><td class="xl68" style="border-top:none;width:48pt" width="64">
</td><td>[3 rows for 1098]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 13</td><td class="xl68" style="border-top:none;width:48pt" width="64">
</td><td>[3 rows for 1098]</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">Row 14</td><td class="xl68" style="border-top:none;width:48pt" width="64">1098</td><td>[original row]
</td></tr></tbody></table>
My VBA knowledge is simply not up to this task. Thanks in advance for any and all help.

Stephen

 

[hiker95]

Eskypades,

One slight change to the macro, just in case there are no matching sku's in worksheet SIZES.


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).

Option Explicit
Sub InsertRowsV2()
' hiker95, 08/08/2011
' http://www.mrexcel.com/forum/showthread.php?t=570073
Dim wI As Worksheet, wS As Worksheet
Dim LR As Long, a As Long, r As Long
Application.ScreenUpdating = False
Set wI = Worksheets("IMPORT")
Set wS = Worksheets("SIZES")
LR = wI.Cells(Rows.Count, 5).End(xlUp).Row
For a = LR To 2 Step -1
  r = Application.CountIf(wS.Columns(1), wI.Cells(a, 5))
  If r > 0 Then wI.Rows(a).Resize(r).Insert
Next a
wI.Activate
Application.ScreenUpdating = True
End Sub

Then run the InsertRowsV2 macro.

 

[Eskypades]

Thanks everyone for your help. James, I tried your code first and it did what I needed. Thanks again.

Stephen

 

[Peter_SSs]

Just some comments on that code. All minor things but may be some use for the future if not now.

While it certainly does what you want and will porobably act very quickly unless you have a large dataset, adding the rows one at a time seems inefficient when each group can be added at once (as the other suggested codes do).

Probably worth turning screen updating off/on to stop any screen flicker (and speed code)

When inserting whole rows, there is no requirement for the "Shift:=xldown" since there is no other direction the rows can go.

So, if using James' code as a basis, I would modify it as follows.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> addRows2()<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("IMPORT")<br>        lRow = .Range("E" & Rows.Count).End(xlUp).Row<br>        <br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = lRow <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1<br>            .Cells(i, 5).Resize(.Cells(i, 6).Value).EntireRow.Insert<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

or even more simply by changing the loop part to:

<font face=Courier New><SPAN style="color:#00007F">For</SPAN> i = lRow <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1<br>    .Rows(i).Resize(.Cells(i, 6).Value).Insert<br><SPAN style="color:#00007F">Next</SPAN> i</FONT>