Insert rows on change of value in Excel table [Solved/Closed]

-
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 vb
System Configuration: Windows XP Internet Explorer 7.0
See more 

1 reply

Best answer
approved by Ratnendra Ashok on Sep 16, 2019
18
Thank you
Hi theHawk,

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.

Best regards,
Trowa

Say "Thank you" 18

A few words of thanks would be greatly appreciated. Add comment

CCM 6096 users have said thank you to us this month

works amazing
Work great, many thanks.
That was the coolest thing I've seen, I know nothing about excel and I was able to follow your instrutions and do it. THANK YOU!
Its just awesome, thanks a lot!!!
amazing! Thanks