Solved Insert Row on Column Value Change

June 6, 2013 at 02:00:06
Specs: Windows 7
Hi,
I have a spreadsheet containing 1000 rows of data, whereby column A is the ID for related rows.
Is it possible to insert a row after each group of related rows?
Ideally, each insert would take the ID of the group it follows (and an arbitrary value into another column).
Thanks,
M

See More: Insert Row on Column Value Change

Report •


#1
Report •

#2
June 6, 2013 at 05:01:00
It worked. Thanks!

But I don't really understand what the code means. I wish it was commented, so I could have gained a better understanding going forward.

Cheers though! :)


Report •

#3
June 6, 2013 at 10:45:18
✔ Best Answer
I've added comments to the existing code (it's not my code), but you should also read what I posted below it.

Sub AddRow()
'Determine the number of Rows in column A that contain data
        NoRows = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Rows, using i as the Row counter
        For i = 2 To NoRows
'Check to see if the current cell is not equal to the cell above it 
'and that both cells are not empty
               If Cells(i, 1) <> Cells(i - 1, 1) _
                   And Cells(i, 1) <> "" _
                   And Cells(i - 1, 1) <> "" Then
'If the cells are not equal and not empty, then insert a row and 
'increment the Row counter to compensate for the new row.
                           Cells(i, 1).EntireRow.Insert
                           i = i + 1
                   End If
         Next

End Sub

OK, now that I hope you can understand the code a little better, I'd like to point out an issue. I had not tested it against actual data before posting the link but I have now and I see a problem.

'Determine the number of Rows in column A that contain data
        NoRows = Range("A" & Rows.Count).End(xlUp).Row

This instruction will set the NoRows variable to the number of rows that contain data before any rows are inserted. As soon as the first row gets inserts, the last row with data will have moved down one. The "last row with data' will continue to move down as each row is inserted.

The problem is that the NoRows variable does not get incremented each time a row is inserted and therefore the code will stop checking once it has checked the original number of rows. Any rows that have moved down below the original number of rows because of the inserts will be ignored. With large groups of data and few inserted rows, it might not matter, but if any changes in the ID numbers get moved down past the original end of the data, they will not be checked and no rows will be inserted.

The version below fixes that problem by looping through the cells in reverse order, ensuring that it checks all values. The code also copies the value at the change point into the new cell and fills the cell with Green to make it easy to spot.

Sub AddRow()
'Determine the number of Rows in column A that contain data
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Rows in reverse, using i as the Row counter
        For i = lastRow To 2 Step -1
'Check to see if the current cell is not equal to the cell above it
'and that both cells are not empty
               If Cells(i, 1) <> Cells(i - 1, 1) _
                   And Cells(i, 1) <> "" _
                   And Cells(i - 1, 1) <> "" Then
'If the cells are not equal and not empty, then...
'
'Insert a row
                        Cells(i, 1).EntireRow.Insert
'Set new cell value equal to cell value from below
                        Cells(i, 1) = Cells(i + 1, 1)
'Fill new cell with Green
                        Cells(i, 1).Interior.ColorIndex = 4
                   End If
         Next

End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 6, 2013 at 11:09:33
Awesome! Thank you SO much! :-)
Clearly explained too.
Cheeky I know. But for each inserted row how would I then set a value to column A and maybe another column too. ;-)

Report •

#5
June 6, 2013 at 12:09:16
re: "But for each inserted row how would I then set a value to column A"

My version of the code already sets a value in Column A: It should be copying the ID from the cell below. In other words, the ID of the group below each new line should appear in the new cell in Column A.

re: "...and maybe another column too"

Am I supposed to guess which column and what value?

Hint: This instruction can be modified to place any value you want into the cell it represents:

Cells(i, 1) = "Whatever"

The syntax of the Cells method is Cells(row_num, col_num)

Cells(1, 1) refers to A1
Cells(5, 4) refers to D5

You can also use the actual column letter if you enclose it in quotes:

Cells(1, "A")
Cells(5, "D")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
June 6, 2013 at 12:47:26
I see. That's great!
All understood.
Thanks again.
M :-)

Report •

Ask Question