Insert a blank row after change in data
 

I have a spreadsheet with 300 rows of data and I need a macro that will
insert a blank row after a change in the data in column B. The data starts
in row 10. Thanks for your help.

Insert a blank row after change in data
 

Came across a sub below by a poster in .programming
Modified a little to end at row 10 to suit.
Try it on a spare copy ..

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 2).End(xlUp).Row To 10 Step -1
If Cells(i - 1, 2) < Cells(i, 2) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Genghis2k3" wrote:
I have a spreadsheet with 300 rows of data and I need a macro that will
insert a blank row after a change in the data in column B. The data starts
in row 10. Thanks for your help.

Insert a blank row after change in data
 

Max,

This works well however the formating of the existing rows is not carried
over to the new row. Can this be done, I am using Vlookup and data validation
in exsisting rows.

"Max" wrote:

Came across a sub below by a poster in .programming
Modified a little to end at row 10 to suit.
Try it on a spare copy ..

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 2).End(xlUp).Row To 10 Step -1
If Cells(i - 1, 2) < Cells(i, 2) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Genghis2k3" wrote:
I have a spreadsheet with 300 rows of data and I need a macro that will
insert a blank row after a change in the data in column B. The data starts
in row 10. Thanks for your help.

Insert a blank row after change in data
 

Hello

Assuming row 10 has the desired formatting and all rows to the new
last row in column B shall have this formatting, this should do it:

Sub InsertRow_At_Change()
Dim i As Long
Dim LastRow As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
For i = LastRow To 10 Step -1
If Cells(i - 1, 2) < Cells(i, 2) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Rows("10").Copy
Rows("10:" & LastRow).PasteSpecial Paste:=xlFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Regards,
Per


On 11 Jan., 16:07, Mike @ GD wrote:
Max,

This works well however the formating of the existing rows is not carried
over to the new row. Can this be done, I am using Vlookup and data validation
in exsisting rows.



"Max" wrote:
Came across a sub below by a poster in .programming
Modified a little to end at row 10 to suit.
Try it on a spare copy ..

Sub InsertRow_At_Change()
Dim i As Long
* * With Application
* * * * .Calculation = xlManual
* * * * .ScreenUpdating = False
* * End With
* * For i = Cells(Rows.Count, 2).End(xlUp).Row To 10 Step -1
* * * * If Cells(i - 1, 2) < Cells(i, 2) Then _
* * * * * * * * Cells(i, 1).Resize(1, 1).EntireRow.Insert
* * Next i
* * With Application
* * * * .Calculation = xlAutomatic
* * * * .ScreenUpdating = True
* * End With
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Genghis2k3" wrote:
I have a spreadsheet with 300 rows of data and I need a macro that will
insert a blank row after a change in the data in column B. *The data starts
in row 10. *Thanks for your help.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -