VBA code to add rows and copy paste data

[exceluser9]

From column A to CL there is data
I wanted to insert a Row whenever there is multiple entries on Column M, Column M will have 9 digit number with square bracket starting and ending of the 9 digit number example 1 - ["088376086"] or it might have example 2 - ["084997875","083948946","083938891"] these 9 digit number will be different and also will be separated with , coma and this 9 digits number will have many records in this example 1 it is 1 entry and in example 2 it has got 3 entries .
Once the Row is inserted based on column M, I wanted vba code to copy and fast the details from column A to L and on Column M it should just paste single 9 digit number like "088376086" and then copy paste the details from Column N to CL.

Thank you

 

[HaHoBe]

Hi exceluser9,

maybe this macro can get you going. It will start at the bottom of the active sheet, find out how many entries in the cell in Column M are located, will either strap the square brackets and place the numbers or insert new rows, copy over all data and fill the values to the corresponding cells in Column M from the array. I feel there must be an easier way to solve this but at the moment I can´t get a hold on it.

Sub procMrE1172221()
'https://www.mrexcel.com/board/threads/vba-code-to-add-rows-and-copy-paste-data.1172221/

'Use this sample on a copy of your data first
'adjust the start row for the data in the constant

Dim lngCounter As Long
Dim lngInsert As Long
Dim varSplit As Variant

Const clngStartRow As Long = 2

Application.ScreenUpdating = False
For lngCounter = Cells(Rows.Count, "M").End(xlUp).Row To clngStartRow Step -1
  With Cells(lngCounter, "M")
    If InStr(1, .Value, ",") > 0 Then
      varSplit = Split(Mid(.Value, 2, Len(.Value) - 2), ",")
      Range("A" & lngCounter + 1).Resize(UBound(varSplit), 1).EntireRow.Insert
      Range("A" & lngCounter).Resize(3, 90).Value = Range("A" & lngCounter).Resize(1, 90).Value
      For lngInsert = LBound(varSplit) To UBound(varSplit)
        Range("M" & lngCounter + lngInsert).Value = varSplit(lngInsert)
      Next lngInsert
    Else
      .Value = Mid(.Value, 2, Len(.Value) - 2)
    End If
  End With
Next lngCounter
Application.ScreenUpdating = True
End Sub

Ciao,
Holger

 

[Peter_SSs]

@HaHoBe
A couple of comments
- I think your .Resize(3,90) needs to be made flexible to match the required number of rows for each section.
- Not that you can transfer your varSplit values to the worksheet all at once rather than via a loop

@exceluser9
This would be my variation of such code, assuming headers in row 1 and data from row 2 in your actual worksheet.

Sub ExpandRows()
  Dim a As Variant, vals As Variant
  Dim i As Long, rws As Long
  
  Application.ScreenUpdating = False
  With Range("A2:CL" & Range("A" & Rows.Count).End(xlUp).Row)
    a = .Columns(13).Value2
    For i = UBound(a) To 1 Step -1
      vals = Split(Mid(a(i, 1), 2, Len(a(i, 1)) - 2), ",")
      rws = UBound(vals) + 1
      If rws > 1 Then
        .Rows(i + 1).Resize(rws - 1).Insert
        .Rows(i).Copy Destination:=.Rows(i).Resize(rws)
      End If
      .Cells(i, 13).Resize(rws).Value = Application.Transpose(vals)
    Next i
  End With
  Application.ScreenUpdating = True
End Sub