CODEWITHSUNDEEP
excelvba
Mahesh Patil
Mahesh Patil

Reputation: 9

Adding new row at the end of each column

I have sheet name called ABC

enter image description here

I need to add new row at the end of each column value. For example in the column Product ID at the end of 'aaa' and at the end of 'bbb' i need to insert copy of aaa to new row at the end of aaa as well copy of bbb to new row at the end of bbb.

Dim cell As Range 
For Each cell In Range("c2:c7") 
  If cell.Value = "aaa" Then 
    cell.EntireRow.Insert 
  End If 
Next cell

But it is inserting after aaa value found, and I need at the end of aaa

Upvotes: 0

Views: 85

Answers (1)

Justyna MK
Justyna MK

Reputation: 3563

You were on a very good track, however, there are two changes that need to be implemented to your code.

The first one is making "aaa" reference dynamic, i.e. you do not want your code to always look for "aaa", but rather always check the "looped" cell's value.

The second change is the loop itself. As your goal is to insert rows, your table range will expand and thus your loop won't serve its purpose. Instead, you should loop from the bottom of the table to the very top.

Here's the code for you to test:

Sub foo()
    Dim lngRow As Long
    
    With Sheet1
        For lngRow = 10 To 3 Step -1
          If .Range("C" & lngRow).Value <> .Range("C" & lngRow + 1).Value Then
            .Range("C" & lngRow + 1).EntireRow.Insert
            .Range("C" & lngRow + 1).Value = .Range("C" & lngRow).Value
          End If
        Next lngRow
    End With
End Sub

Here's the result after running the code:

enter image description here

Upvotes: 1

Related Questions