Formula help...to insert rows

[Nhunter]

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

 

[Nhunter]

The first

 

[Nhunter]

Here are the first lines of what I am working with...not sure if there is an issue there I am not seeing.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Internal ID			Name	Display Name	Store Description	Description	Type	Base Price	Display in Web Site	Category	Inactive	Manufacturer	Store Description2
2	278		0	1422V420	Motor Drive Belt	This is a replacement motor drive belt, part# 1422V420.	Motor Drive Belt #1422V420	Inventory Item	34.95	Yes	Products and Services > Motor Drive Belts	No		This is a replacement motor drive belt, part# 1422V420.
3	3894		0	PETL515050 : Proform 595v : Drv Mtr	PROFORM 595V TREADMILL Drive Motor	PETL515050	Proform 595v Treadmill Drive Motor:235170	Inventory Item	319.99	Yes	Products and Services > Drive Motors > Proform Treadmill Motors	No	PROFORM	This Is The Replacement Drive Motor For The Proform 595v Treadmill.For Model Numbers: PETL515050
4	9044		0	PROFORM XP 580 X-TRAINER:189462	PROFORM XP 580 X-TRAINER Drive Belt	246450	PROFORM XP 580 X-TRAINER Motor Drive Belt:189462	Inventory Item	29.99	Yes	Products and Services > Motor Drive Belts > PROFORM Motor Drive Belts	No	PROFORM	This the replacement motor drive belt for the PROFORM XP 580 X-TRAINER. For Model Numbers: 246450
5	9045		0	PROFORM XP 580S X-TRAINER:189462	PROFORM XP 580S X-TRAINER Drive Belt	246550	PROFORM XP 580S X-TRAINER Motor Drive Belt:189462	Inventory Item	29.99	Yes	Products and Services > Motor Drive Belts > PROFORM Motor Drive Belts	No	PROFORM	This the replacement motor drive belt for the PROFORM XP 580S X-TRAINER. For Model Numbers: 246550
6	9049		1	PROFORM 770EKG TREADMILL:186689	PROFORM 770EKG TREADMILL Drive Belt	PCTL99010 : PCTL99011	PROFORM 770EKG TREADMILL Motor Drive Belt:186689	Inventory Item	39.99	Yes	Products and Services > Motor Drive Belts > PROFORM Motor Drive Belts	No	PROFORM	This the replacement motor drive belt for the PROFORM 770EKG TREADMILL. For Model Numbers: PCTL99010 : PCTL99011
7	9058		2	PROFORM 520X TREADMILL:186689	PROFORM 520X TREADMILL Drive Belt	DRTL59220 : DRTL59221 : DRTL59222	PROFORM 520X TREADMILL Motor Drive Belt:186689	Inventory Item	39.99	Yes	Products and Services > Motor Drive Belts > PROFORM Motor Drive Belts	No	PROFORM	This the replacement motor drive belt for the PROFORM 520X TREADMILL. For Model Numbers: DRTL59220 : DRTL59221 : DRTL59222
8	9084		0	PROFORM 495PI TREADMILL:224019	PROFORM 495PI TREADMILL Drive Belt	PCTL952350	PROFORM 495PI TREADMILL Motor Drive Belt:224019	Inventory Item	29.99	Yes	Products and Services > Motor Drive Belts > PROFORM Motor Drive Belts	No	PROFORM	This the replacement motor drive belt for the PROFORM 495PI TREADMILL. For Model Numbers: PCTL952350
CustomItemSearchResults463(1)

 

[VoG]

This will skip rows containing 0 in column C

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)
        If .Value <> 0 And .Value <> "" Then
            .Offset(1).Resize(.Value).EntireRow.Insert
            .EntireRow.Copy Destination:=.Offset(1, -2).Resize(.Value)
            X = Split(.Offset(, 3), ":")
            .Offset(, 1).Value = Trim(X(0)) & ": " & .Offset(, 1).Value
            For j = 1 To UBound(X)
                .Offset(j, 1).Value = Trim(X(j)) & ": " & .Offset(j, 1).Value
            Next j
        End If
    End With
Next i
Columns("D").AutoFit
End Sub

 

[Nhunter]

Is there a way to include the rows with zero? They need the information as well.

 

[VoG]

Try

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)
        If .Value <> 0 And .Value <> "" Then
            .Offset(1).Resize(.Value).EntireRow.Insert
            .EntireRow.Copy Destination:=.Offset(1, -2).Resize(.Value)
        End If
        X = Split(.Offset(, 3), ":")
        .Offset(, 1).Value = Trim(X(0)) & ": " & .Offset(, 1).Value
        For j = 1 To UBound(X)
            .Offset(j, 1).Value = Trim(X(j)) & ": " & .Offset(j, 1).Value
        Next j
    End With
Next i
Columns("D").AutoFit
End Sub

 

[Nhunter]

Returns the 400 error

 

[VoG]

I don't get that error. Which line of code is highlighted?

 

[Nhunter]

You are the greatest! Thanks for the help. I know I was not the clearest communicator with regard to my issue!

Best regards,
Norman