CODEWITHSUNDEEP
excelvba
franklingranger
franklingranger

Reputation: 35

Repeat column contents by "n" rows based on column value

I would like to repeat ID number based on the "number" number. For example:

enter image description here

to

enter image description here

I have tried the following so far..

  Sub MySub()
  Do While B2 = n
    CurrentSheet.Range("a1:c1").EntireRow.Resize(n).Insert
  Loop
  End Sub

It probably doesn't make much sense, as I am fairly new!

Upvotes: 0

Views: 45

Answers (1)

SJR
SJR

Reputation: 23081

If you wanted to list the data in column D, you could use this

Sub x()

Dim r As Range

For Each r In Range("A2", Range("A" & Rows.Count).End(xlUp))                         'loop through A
    Range("D" & Rows.Count).End(xlUp)(2).Resize(r.Offset(, 1).Value).Value = r.Value 'duplicate number of times in B
Next r

End Sub

If you want to insert into your existing data

Sub x()

Dim r As Long

For r = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Cells(r, 2) > 1 Then
        Cells(r + 1, 1).EntireRow.Resize(Cells(r, 2).Value - 1).Insert shift:=xlDown
        Cells(r + 1, 1).Resize(Cells(r, 2).Value - 1) = Cells(r, 1).Value
    End If
Next r

End Sub

Upvotes: 2

Related Questions