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
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.
Can this code be enhanced so that it sets the colour of the cells in the inserted row to black for all the columns in the table.
The table goes from column A to Column H.
I inserted module to spreadsheet and it worked great.
Thanks again
The table goes from column A to Column H.