Formula help...to insert rows

Nhunter

New Member
Joined
Mar 24, 2009
Messages
17
I am hoping to be able to insert a number of rows via formula. I am having issues coming up with anything on my own. I am hoping to duplicate(and insert) a specific row (x) number of times based on a number returned from a previous formula.

Any help would be greatly appriciated.

Best regards,
Nhunter
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hello and welcome to MrExcel.

Sorry, that isn't possible using formulas.

It may be possible using VBA. Can you be more specific about which cell contains the formula and where the rows are to be inserted.
 

Nhunter

New Member
Joined
Mar 24, 2009
Messages
17
Certainly...

I have a spreadsheet that contains specfic products. The sheet has numerous instances where there are multiple ITEM NAMES (these are model number) contained within a single STORE DESCRIPTION (here the model numbers are seperated by colons). I have need to create a new row for each model number based on the data from the original minus the other model numbers.
 

Nhunter

New Member
Joined
Mar 24, 2009
Messages
17

ADVERTISEMENT

Excel Workbook
BCDF
1Item Name/NumberNameStore Description
811PROFORM 785 TREADMILL:224021PFTL19040 : PFTL19041
824PROFORM 785 XT TREADMILL:186689PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864
831PROFORM CROSSWALK CSI TREADMILL:186689PFTL21460 : PFTL21462
842PROFORM 345 TREADMILL:224019PFTL311040 : PFTL311041 : PFTL311042
851PROFORM 330i TREADMILL:224019PFTL313040 : PFTL313041
862PROFORM 495 Pi TREADMILL:227178PFTL331050 : PFTL331051 : PFTL331052
873PROFORM 595 Pi TREADMILL:227178PFTL431050 : PFTL431051 : PFTL431052 : PFTL431053
CustomItemSearchResults212(1)
 

Nhunter

New Member
Joined
Mar 24, 2009
Messages
17
SO looking at the above example I have determined that for row 81 I need a duplicate row created. Additionally I would need to cocantenate Cell F, the information after the colon (first and only in this case), with cell D in the new row is possible.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

I think that this will do partly what you want (not the concatenate bit).

Try this with a copy of your sheet. Press ALT + F11 to open the Visual Basic Editor, Insert Module and paste the following into the white space on the right:

Code:
Sub InsRows()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    With Range("C" & i)
        .Offset(1).Resize(.Value).EntireRow.Insert
        .EntireRow.Copy Destination:=.Offset(1, -2).Resize(.Value)
    End With
Next i
End Sub

Press ALT + F11 to return to your sheet, Tools > Macro > Macros, highlight InsRows and click the Run button.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Added the concatenation :)

Code:
Sub InsRows()
Dim LR As Long, i As Long, j As Integer, X
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    With Range("C" & i)
        .Offset(1).Resize(.Value).EntireRow.Insert
        .EntireRow.Copy Destination:=.Offset(1, -2).Resize(.Value)
        X = Split(.Offset(, 3), ":")
        .Offset(, 1).Value = .Offset(, 1).Value & " " & X(0)
        For j = 1 To UBound(X)
            .Offset(j, 1).Value = .Offset(j, 1).Value & X(j)
        Next j
    End With
Next i
End Sub
 

Nhunter

New Member
Joined
Mar 24, 2009
Messages
17
That indeed inserted the copied rows. Thanks for helping with that. My issue now is eliminating one item and the subsequent colon from column F in each new row.

Any thoughts?

Best regards,
Norman
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Here's a shot of the sheet after inserting rows. Can you show us what you would like column F to look like.

<b>Sheet5 (2)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:367px;" /><col style="width:43px;" /><col style="width:372px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td >Item Name/Number</td><td > </td><td >Name</td><td > </td><td >Store Description</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td style="text-align:right; ">1</td><td >PROFORM 785 TREADMILL:224021 PFTL19040 </td><td > </td><td >PFTL19040 : PFTL19041</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td style="text-align:right; ">1</td><td >PROFORM 785 TREADMILL:224021 PFTL19041</td><td > </td><td >PFTL19040 : PFTL19041</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:right; ">4</td><td >PROFORM 785 XT TREADMILL:186689 PFTL20860 </td><td > </td><td >PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="text-align:right; ">4</td><td >PROFORM 785 XT TREADMILL:186689 PFTL20861 </td><td > </td><td >PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="text-align:right; ">4</td><td >PROFORM 785 XT TREADMILL:186689 PFTL20862 </td><td > </td><td >PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td style="text-align:right; ">4</td><td >PROFORM 785 XT TREADMILL:186689 PFTL20863 </td><td > </td><td >PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td style="text-align:right; ">4</td><td >PROFORM 785 XT TREADMILL:186689 PFTL20864</td><td > </td><td >PFTL20860 : PFTL20861 : PFTL20862 : PFTL20863 : PFTL20864</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td style="text-align:right; ">1</td><td >PROFORM CROSSWALK CSI TREADMILL:186689 PFTL21460 </td><td > </td><td >PFTL21460 : PFTL21462</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td style="text-align:right; ">1</td><td >PROFORM CROSSWALK CSI TREADMILL:186689 PFTL21462</td><td > </td><td >PFTL21460 : PFTL21462</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

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
Top