CODEWITHSUNDEEP
vbaexcel
Rilind Maqi
Rilind Maqi

Reputation: 1

Add value of next row and column on previous row

I have an Excel document at work with tables with rows and columns that resembles the picture I've included (not the content). I want to do two things, one of them is adding another row after a distinct value in the column as you can see in the picture, which I've done (code included). But the next step is putting the value of the column in the next row after the blank/empty row on the added row (blank) as a value as pictured with the values Small, Med, Large.

I'm not a die hard programmer but I'm learning VBA for my job and this is the code that i found on this site and works for adding a blank row:

Dim LastRow As Long
Dim i As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = LastRow To 2 Step -1
    If i = 2 Then
        'Do nothing
    ElseIf Cells(i, "A") <> Cells(i - 1, "A") Then
        Cells(i, "A").EntireRow.Insert
    End If
Next i

Can you help me with the adding of the value of the next row?

Current and Desired output

Upvotes: 0

Views: 3840

Answers (1)

Rik Sportel
Rik Sportel

Reputation: 2679

You already loop upwards through the populated cells. Whenever you find a cell where column A had another value than the cell above, you insert a row. Within the if block, just take the value from column B, so you get the code below. Also, why do nothing when you arrived at row 2? Since you want to insert another empty one above, it should just be included. End result:

Dim LastRow As Long
Dim i As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = LastRow To 2 Step -1
    If Cells(i, "A") <> Cells(i - 1, "A") Then
        Cells(i, "A").EntireRow.Insert
        Cells(i, "B").Value = Cells(i + 1, "B").Value 'i + 1 would be the next row
    End If
Next i

Upvotes: 1

Related Questions