Insert rows on change of value in Excel tableSolved/Closed

Question

Hello,
I have an excell 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 vbSystem Configuration: Windows XP Internet Explorer 7.0

Trowa · Accepted Answer

Hi theHawk,

Have you tried a google search?

I found this site:
https://www.mrexcel.com/board/threads/insert-rows-between-different-data-2.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 iEnd 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.

Best regards,
Trowa

Insert rows on change of value in Excel table

1 reply

Subscribe To Our Newsletter!