Number Range Field in Excel Expanded into identical rows for the length of the number Range

You have a protected worksheet and a protected workbook structure. As a result, a macro won't be able to modify your worksheet--unless you post a password.

Assuming that you unprotect your worksheet, and that the number after the dash in column H represents the number of row copies, here is a macro to do the duplication. The macro assumes that a number without a dash in column H also represents the number of row copies you want.

As written, the code converts the active worksheet (Starting point) into one that resembles Results in the attached workbook.

Sub RowDuper()
Dim rg As Range
Dim s As String
Dim i As Long, ii As Long, n As Long, nn As Long
Dim v As Variant
Application.ScreenUpdating = False
Set rg = Range("A1").CurrentRegion
n = rg.Rows.Count
For i = n To 2 Step -1
    s = rg.Cells(i, "H").Value
    If s <> "" Then
        nn = Split(s & "-" & s, "-")(1)
        If nn > 1 Then
            rg.Rows(i + 1).Resize(nn - 1, 1).EntireRow.Insert
            For ii = 2 To nn
                rg.Rows(i + ii - 1).Value = rg.Rows(i).Value
            Next
        End If
    End If
Next
End Sub

Select all Open in new window

RowDuperQ28974104.xlsm