Insert formula

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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
 
This is amazing. Thank you so much Jacob.

Jacob Skaria said:
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
Click to expand...
 
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
 
Back
Top