Insert rows on change of value in Excel table
Solved/Closed
Related:
- Insert rows based on cell value
- Excel macro to insert rows between data - Best answers
- Insert blank row when value changes - Best answers
- Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
- Insert draft watermark in word on all pages - Guide
- Insert key on laptop - Guide
- Based on the value in cells b77 b81 ✓ - Excel Forum
- Insert check mark in word - Guide
1 response
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:
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
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 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
Dec 18, 2009 at 09:01 AM
I inserted module to spreadsheet and it worked great.
Thanks again
Dec 18, 2009 at 09:11 AM
The table goes from column A to Column H.