Insert # of Rows based on Count

Eskypades

Eskypades

Board Regular
Joined
Nov 19, 2009
Messages
98
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Eskypades,

It would really help us if you were to give us some screenshots with one of the utilities listed below so that we do not have to separate your screenshots manually.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.
 
Upvote 0
Hi,

Try:

Code: 
Sub addRows()
    Dim rng_SIZES   As Range
 
    Dim i           As Long, _
        j           As Long, _
        lRow        As Long, _
        lng_NumRows As Long
 
    Dim str_LookupVal As String
 
    With Sheets("SIZES")
        Set rng_SIZES = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
 
    With Sheets("IMPORT")
        lRow = .Range("E" & Rows.Count).End(xlUp).Row
 
        For i = lRow To 2 Step -1
            str_LookupVal = .Cells(i, 5).Value
            lng_NumRows = Application.WorksheetFunction.CountIf(rng_SIZES, str_LookupVal)
 
            For j = 1 To lng_NumRows
                .Cells(i, 5).Insert Shift:=xlDown
            Next j
        Next i
    End With
End Sub
 
Upvote 0
Thanks hiker. I can't seem to edit my original post, so I'll put the tables here in the order I put them in the first post.

Excel Workbook
E
1sku
2631
3633
41095
51098
Sheet2
Excel Workbook
AB
1skusize-id
26312441
36332460
410954926
5109519310
610954927
710954928
810984944
910984942
1010984943
Excel 2010 Sheet2
Excel Workbook
EF
1sku[notes]
2[1 row for 631]
3631[original row]
4[1 row for 633]
5633[original row]
6[4 rows for 1095]
7[4 rows for 1095]
8[4 rows for 1095]
9[4 rows for 1095]
101095[original row]
11[3 rows for 1098]
12[3 rows for 1098]
13[3 rows for 1098]
141098[original row]
Excel 2010 Sheet2
Excel 2010


Thanks again,
Stephen
 
Upvote 0
Perhaps this? Test in a copy of your workbook.

My code assumes that column F in 'IMPORT' is available to use as a helper column. That can easily be changed to another column if F contains data.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> InsertSKUrows()<br>    <SPAN style="color:#00007F">Dim</SPAN> lrI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lrS <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("SIZES")<br>        lrS = .Range("A" & .Rows.Count).End(xlUp).Row<br>    End <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("IMPORT")<br>        lrI = .Range("E" & Rows.Count).<SPAN style="color:#00007F">End</SPAN>(xlUp).Row<br>        .Range("F2:F" & lrI).Formula = _<br>            "=COUNTIF(SIZES!A$1:A$" & lrS & ",E2)"<br>        <SPAN style="color:#00007F">For</SPAN> r = lrI <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1<br>            .Rows(r).Resize(.Cells(r, "F").Value).Insert<br>        <SPAN style="color:#00007F">Next</SPAN> r<br>        .Columns("F").ClearContents<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Last edited:
Upvote 0
James,

I just realized that I didn't clarify one important point. It looks like the code you posted should work, but I forgot to mention that I need an entire row inserted and not just in column E. I'm not sure how this would change in the code you've posted.

Also, I'm not sure if it would help the calculation time, but I can put a COUNTIF formula in the IMPORT tab that will count how many times a certain SKU is found in the SIZES tab, and then have the VBA code reference that value instead of performing the count in the code.

Thanks again,
Stephen
 
Upvote 0
Eskypades,


Thank you for the scfreenshots.


Sample raw data:


Excel Workbook
AB
1skusize_id
26312441
36332460
410954926
5109519310
610954927
710954928
810984944
910984942
1010984943
11
SIZES





Excel Workbook
E
1sku
2631
3633
41095
51098
6
7
8
9
10
11
12
13
14
15
IMPORT





After the macro:


Excel Workbook
E
1sku
2
3631
4
5633
6
7
8
9
101095
11
12
13
141098
15
IMPORT





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.


Code: 
Option Explicit
Sub InsertRows()
' 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))
  wI.Rows(a).Resize(r).Insert
Next a
wI.Activate
Application.ScreenUpdating = True
End Sub


Then run the InsertRows macro.
 
Upvote 0
Eskypades said:
Also, I'm not sure if it would help the calculation time, but I can put a COUNTIF formula in the IMPORT tab that will count how many times a certain SKU is found in the SIZES tab, and then have the VBA code reference that value instead of performing the count in the code.
Click to expand...
By now you've probably noticed that my code does just that. :biggrin:
 
Upvote 0
Hi,

Change .Insert to .EntireRow.Insert.

You can add an =CountIF function if you want. Depending on how many rows you have it won't make a blind bit of difference in terms of calculation speed.

If you do, then try:

Code: 
Sub addRows2()
    With Sheets("IMPORT")
        lRow = .Range("E" & Rows.Count).End(xlUp).Row
        
        For i = lRow To 2 Step -1
            For j = 1 To Cells(i, 6).Value
                .Cells(i, 5).EntireRow.Insert Shift:=xlDown
            Next j
        Next i
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
P
  • Solved
Split rows separated by blank row and create multiple files
Replies
8
Views
172
Kaper
Kaper
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,588
Messages
6,179,743
Members
452,940
Latest member
rootytrip

Share this page

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
Back
Top