Macro to copy and insert rows based on the value in a cell

[WARitchie]

Hi, I'm trying to teach myself to write in VBA but many hours have shown this problem is way above my abilities at the moment. I have a very long list in a worksheet where column F contains one or more pipe diameter, and if there is more than one diameter for that particular line the cell contains multiple values separated by a comma.

I would like to use a macro to look for cells that contain more than one value, copy that entire row and then insert the copied rows below as many times as there are pipe diameters, e.g. a row with cell F1 containing value "2, 4, 6" would be copied twice below. If it helps, I've written a formula that counts the number of commas in each cell (i.e. the number of times to insert rows below the original) which is placed in column B.

Could anyone help me with this?

 

[WARitchie]

If anyone else has had a similar problem, I haven't solved this but have made it half-way by going through other threads. I used the VBA script below which meant I could move to a row, hit the shortcut to the macro, enter the number of lines I wanted, then I'd manually move down to the next instance. It was still slow but saved me some time. I'm still sure there's a way to improve this macro and do the lot in a second (rather than half an hour). Please do post a better answer if you have one though.

Sub CopyXLinesBelow()Dim x As Integer
  x = Application.InputBox("Number of Rows", "Number of Rows", Type:=1)
  If x = False Then Exit Sub
    ActiveCell.EntireRow.Copy
    Range(ActiveCell.Offset(1), ActiveCell.Offset(x)).EntireRow.Insert
    Application.CutCopyMode = False
End Sub