Insert formula

[John]

Hi all,

Is there a way to insert a row automatically in between a different number
when the number change?. Like creating a macro or something. I do not want to
do it manually. I have about 200 rows to insert. Please see example below. I
want to be able to insert a row in between 2000045 and 200061 also between
2000061 and 2000069 and so on.....

2000045
2000045
2000045
2000061
2000061
2000061
2000069
2000069
2000076
2000076
2000076
2000087
2000087

 

[Jacob Skaria]

Try one of these macros..with data in ColA data starting at Row 2

If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub InsertBlankRowAfterEachGroup()
Dim lngRow As Long, intTemp As Integer
lngRow = 2
Do
If Range("A" & lngRow + 1) <> Range("A" & lngRow) Then _
Rows(lngRow + 1).Insert: lngRow = lngRow + 1
lngRow = lngRow + 1
Loop Until lngRow > Cells(Rows.Count, "A").End(xlUp).Row
End Sub


Sub InsertBlankRowsbetweenChangingValues()
Dim lngRow As Long
Application.ScreenUpdating = False
For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> "" And Range("A" & lngRow - 1) <> "" And _
Range("A" & lngRow) <> Range("A" & lngRow - 1) Then Rows(lngRow).Insert
Next
Application.ScreenUpdating = True
End Sub

 

[John]

This is amazing. Thank you so much Jacob.

Try one of these macros..with data in ColA data starting at Row 2

If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub InsertBlankRowAfterEachGroup()
Dim lngRow As Long, intTemp As Integer
lngRow = 2
Do
If Range("A" & lngRow + 1) <> Range("A" & lngRow) Then _
Rows(lngRow + 1).Insert: lngRow = lngRow + 1
lngRow = lngRow + 1
Loop Until lngRow > Cells(Rows.Count, "A").End(xlUp).Row
End Sub


Sub InsertBlankRowsbetweenChangingValues()
Dim lngRow As Long
Application.ScreenUpdating = False
For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> "" And Range("A" & lngRow - 1) <> "" And _
Range("A" & lngRow) <> Range("A" & lngRow - 1) Then Rows(lngRow).Insert
Next
Application.ScreenUpdating = True
End Sub

 

[Gord Dibben]

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 2 Step -1
If Cells(X, 1).Value <> Cells(X - 1, 1).Value Then
If Cells(X, 1).Value <> "" Then
If Cells(X - 1, 1).Value <> "" Then
Cells(X, 1).EntireRow.Insert
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP