Insert rows on change of value in excel table

July 2017


Insert rows on change of value in excel table




Issue


Hello,
I have an excel spreadsheet that contains a table. I want a macro of VB to insert a row into the table when the values in a particular column change.
Column A
1
1
1
2
2
3
3
3

would convert to
1
1
1

2
2

3
3
3
any suggestions welcome. I have limited experience in using macros and almost none with VB

Solution


Have you tried a google search?

I found this site:
http://www.mrexcel.com/forum/showthread.php?t=58685

Which provides the following VBA code which seems to work just fine:
Sub InsertRows()
  Dim r As Long, mcol As String, i As Long

' find last used cell in Column A
  r = Cells(Rows.Count, "A").End(xlUp).Row

 ' get value of  last used cell in column A
  mcol = Cells(r, 1).Value

 ' insert rows by looping from bottom
  For i = r To 2 Step -1
     If Cells(i, 1).Value <> mcol Then
       mcol = Cells(i, 1).Value
        Rows(i + 1).Insert
     End If
  Next i

End Sub




How to use this code:
Go to Extra>Macro>Visual Bacis Editor (Alt+F11)
A new window will open.
Go to Insert>Module
Copy and paste the above code in the great white space.
Close the window.
Back in your excel window.
Go to Extra>Macro>Macro's (Alt+F8)
A new small window will open.
Double click "InsertRows" to see the result of the code.

If something is still unclear then post back.

Note


Solved by Trowa

Related


Published by aakai1056. Latest update on January 14, 2011 at 01:56 AM by aakai1056.
This document, titled "Insert rows on change of value in excel table," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).