Menu
Ask a question Report

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

-
Excel Ask a question Report
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
Report
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

View all 12 comments
Report
-
works amazing
Report
-
Work great, many thanks.
Report
-
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!
Report
-
Its just awesome, thanks a lot!!!
Report
-
amazing! Thanks
Ask a question