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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,983
Messages
5,834,707
Members
430,311
Latest member
JAC0617

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