Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •