Excel Loop through worksheet to create new rows for parent item

Question

I need some help in creating a simple VBA to create a parent row for a list of items with variables.

my screenshot

Refer to my screenshot here

As seen in the screenshot, right now my data is similar to the "before" table. I am trying to create a VBA script which loops through all the rows and creates new row based on the group. I want to create a new row for every group number, and on that new row, it copies certain values from the cell below it.

Thanks! Nelson

You could make a piece of code which iterates through your worksheet, looks for text in column "J". When found, inserts a row above and moves the text there as well. You can use this:http://stackoverflow.com/questions/17574969/looping-through-all-rows-in-a-table-column-excel-vba and this: http://stackoverflow.com/questions/15816883/excel-vba-inserting-blank-row-and-shifting-cells — Wouter, Feb 26 '16 at 12:12
First you need to make sorting for column named SKU then in vba code, make while loop if the e3 = 24 then "do nothing" else insert new row and add p to the sku code. hopefully its clear — Mohammed, Feb 26 '16 at 12:43

TheGuyThatDoesn'tKnowMuch · Answer 1 · 2016-02-26T17:47:25.100

Try this:

Sub Add_Row()

Range("I3").Select 'This assumes the first row of data after column headers is row 3

While ActiveCell <> ""

If ActiveCell.Offset(0, 1).Value <> "" Then

Selection.EntireRow.Insert

ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(1, 1).Value
ActiveCell.Offset(1, 1).ClearContents
ActiveCell.Offset(0, -3).Value = ActiveCell.Offset(1, -3).Value
ActiveCell.Offset(0, -4).Value = ActiveCell.Offset(1, -4).Value & "P"

ActiveCell.Offset(1, 0).Select

Else

ActiveCell.Offset(1, 0).Select

End If

Wend

Range("A1").Select

End Sub

score 0 · Answer 2 · answered Feb 26 '16 at 13:35

You can insert blank rows like this:

Sub Macro1()
    Dim i As Long
    i = 3    

    Do While Cells(i, 1) <> ""
      If Cells(i, 1) <> Cells(i - 1, 1) Then
        Rows(i).Insert Shift:=xlDown
        i = i + 1
      End If
      i = i + 1
    Loop
End Sub

Hopefully changing the cells should not be an issue now

score 0 · Answer 3 · answered Feb 26 '16 at 14:23

the following code should let you change it easily to your current and, possibly, future needs

I assumed, as per your linked example, that "Description" column always has one non blank cell at the beginning of every "Group" or "SKU" rows block

Sub CreateRowForParentItem()

Dim sht As Worksheet
Dim cell As Range
Dim descriptionCol As Long, SKUCol As Long, productCol As Long

'------------------------------
' setting stuff - begin
descriptionCol = 10 '<== adapt it to your actual "Description" column number
SKUCol = 5          '<== adapt it to your actual "SKU" column number
productCol = 6      '<== adapt it to your actual "Product Title" column number

Set sht = ThisWorkbook.Sheets("SheetFruit") '<== change 'data' sheet as per your needs
' setting stuff - end
'------------------------------


'------------------------------
' core code - begin
With sht
    Set cell = .Cells(.Rows.Count, descriptionCol).End(xlUp) '<== find last non blank cell in "Description" column
    Do While cell.value <> "Description" '<== proceed only if it's not the header cell
        cell.EntireRow.Insert
        Call CopyAndClearRange(.Cells(cell.row, SKUCol))
        Call CopyAndClearRange(.Cells(cell.row, productCol))
        Call CopyAndClearRange(.Cells(cell.row, descriptionCol), True)

        Set cell = .Cells(cell.row - 1, descriptionCol).End(xlUp) '<== find next non blank cell up
    Loop
End With
' core code - end
'------------------------------

End Sub


Sub CopyAndClearRange(rng As Range, Optional okClear As Variant)

If IsMissing(okClear) Then okClear = False
With rng
    .Copy .Offset(-1)
    If okClear Then .Clear
End With

End Sub

Excel Loop through worksheet to create new rows for parent item

my screenshot

3 Answers3