Creating a new row for each item in a cell (Excel 2000, Win 2k)

Topic

New Reply

OK, I have a number of work sheets that have alpha characters in one cell in the row. What I would like to be able to do is to create a new row, that same as the original, but with only one of the alpha characters in it.

Say cell M2 has the values A, B, C (comma separated as shown), Cells A2 to L2 have various values in, I want to replicate cells A2 to L2 twice, so that I now have A2 to L2, A3 to L3, A4 to L4 all the same, then in M2, I have A, M3 would be B, M2 would be C.

I’ve been doing this so far by copy and paste, then deleting the extra characters, it has now become some what boring so I wondered if there was a clever way of doing it?

Thanks for reading, hope it makes sense.

Ian

Reply | Quote

Replies

You can use a macro. Assuming that (1) there are no “used” rows below the ones you want to process and (2) row 1 contains column headers, you can use this:

Sub SplitRows()
Dim i As Long
Dim j As Long
Dim n As Long
Dim arr() As String
‘ Last used row
n = Range(“M65536”).End(xlUp).Row
‘ Loop backwards
For i = n To 2 Step -1
‘ Split value of cell in column M
arr = Split(Range(“M” & i), “, “)
‘ If more than 1 component
If UBound(arr) > 0 Then
Range(“M” & i) = arr(0)
For j = UBound(arr) To 1 Step -1
Range(“A” & i & “:M” & i).Copy
Range(“A” & (i + 1) & “:M” & (i + 1)).Insert Shift:=xlDown
Range(“M” & (i + 1)) = arr(j)
Next j
End If
Next i
Application.CutCopyMode = False
End Sub

See attached demo.

Reply | Quote

Thanks Hans

Had to do a little bit of modifying (I’d not mentioned the columns after M……) but it’s just completed in ten minutes wht I’d spent most of Friday afternoon starting off.

Thanks

Ian

Reply | Quote