Auto Row insert if there is different number in the cell - Macro required

[harinsh]

Hi All,

I hope can anyone help me with the below requirement.

I have data as below and need to insert one new row whenever number changes. This should happen through macro because my data would be very huge like 500 to 1000 rows so, sometimes it goes beyond this number.

Ex:-
Column 1
650
650
650
690
690
680
680

Out put Should be
Column 1
650
650
650

690
690

680
680

Kindly help me on this and provide your suggestions.

Thank you.

 

[hiker95]

harinsh,

Sample raw data:

Excel 2007
	A
1	650
2	650
3	650
4	690
5	690
6	680
7	680
8
9
10
Sheet1

After the macro:

Excel 2007
	A
1	650
2	650
3	650
4
5	690
6	690
7
8	680
9	680
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub InsertRowOnChange()
' hiker95, 0515/2013
' http://www.mrexcel.com/forum/excel-questions/702983-auto-row-insert-if-there-different-number-cell-macro-required.html
Dim r As Long, lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
  If Cells(r - 1, 1) <> Cells(r, 1) Then Rows(r).Insert
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the InsertRowOnChange macro.

 

[harinsh]

Thanks you very much for your inputs....I need small customization here and hope you can help me...

I want to insert new row if you find different value/text in the cell and also if we get empty cell then it should exclude that row and continue the task.

[TABLE="width: 169"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Input
[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A1[/TD]
[/TR]
[TR]
[TD]1212[/TD]
[TD]1212
[/TD]
[/TR]
[TR]
[TD]1313[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1212[/TD]
[TD]1313[/TD]
[/TR]
[TR]
[TD]1212[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1212[/TD]
[/TR]
[TR]
[TD]5454[/TD]
[TD]1212[/TD]
[/TR]
[TR]
[TD]5454[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6666[/TD]
[TD]5454[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5454[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6666[/TD]
[/TR]
</tbody>[/TABLE]


Let me know if you need further info.

 

[hiker95]

harinsh,

You are very welcome. Glad I could help.

I want to insert new row if you find different value/text in the cell and also if we get empty cell then it should exclude that row and continue the task.

Sample raw data:

Excel 2007
	A
1	1212
2	1313
3	1212
4	1212
5
6	5454
7	5454
8	6666
9
10
11
12
Sheet1

After the new macro:

Excel 2007
	A
1	1212
2
3	1313
4
5	1212
6	1212
7
8	5454
9	5454
10
11	6666
12
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub InsertRowOnChange_V2()
' hiker95, 05/30/2013
' http://www.mrexcel.com/forum/excel-questions/702983-auto-row-insert-if-there-different-number-cell-macro-required.html
Dim r As Long, lr As Long
Application.ScreenUpdating = False
On Error Resume Next
Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
  If Cells(r - 1, 1) <> Cells(r, 1) Then Rows(r).Insert
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the InsertRowOnChange_V2 macro.

 

[harinsh]

Thanks for your code...I want to ignore the row and not to delete because I have done same segregation for different parameter so, if I delete the row then those changes will go off...Please provide updated code....Thanks

 

[hiker95]

harinsh,

I want to ignore the row and not to delete because I have done same segregation for different parameter

You should have mentioned this parameter from the beginning.

Sample raw data:

Excel 2007
	A
1	1212
2	1313
3	1212
4	1212
5
6	5454
7	5454
8	6666
9
10
11
12
Sheet1

After the new macro:

Excel 2007
	A
1	1212
2
3	1313
4
5	1212
6	1212
7
8	5454
9	5454
10
11	6666
12
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub InsertRowOnChange_V3()
' hiker95, 05/31/2013
' http://www.mrexcel.com/forum/excel-questions/702983-auto-row-insert-if-there-different-number-cell-macro-required.html
Dim r As Long, lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
  If Cells(r, 1) = "" Or Cells(r - 1, 1) = "" Then
    'do nothing
  ElseIf Cells(r - 1, 1) <> Cells(r, 1) Then
    Rows(r).Insert
  End If
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the InsertRowOnChange_V3 macro.

 

[harinsh]

Hi Hiker95.

Your really genius and it is working exactly as per my requirement....

Thank you very much for your efforts....

Have a great day

 

[hiker95]

harinsh,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[harinsh]

Hi Hiker95, I'm looking for another requirement can you able to help on that? Find the below link
http://www.mrexcel.com/forum/excel-...number-line-items-using-different-sheets.html
Thanks