Inserting a Blank Line

[Saxman]

I did ask for similar a little while back, but cannot somehow digest the
code.

What is the best way to insert a blank line where the data changes?

The data is in column B.

e.g.

14:45:00
14:45:00
14:45:00
14:45:00
15:00:00
15:00:00
15:00:00
15:00:00
15:00:00
15:15:00
15:15:00
15:15:00
15:15:00
15:15:00

to..

14:45:00
14:45:00
14:45:00
14:45:00

15:00:00
15:00:00
15:00:00
15:00:00
15:00:00

15:15:00
15:15:00
15:15:00
15:15:00
15:15:00

 

[Gord]

Don't know if this is best, but certainly an easy method.

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 Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub

Copy the code and paste into a module in your workbook.

As written it operates on column A


Gord Dibben Microsoft Excel MVP

 

[Saxman]

Don't know if this is best, but certainly an easy method.

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 Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub

Copy the code and paste into a module in your workbook.

As written it operates on column A

Thank you.

Cannot get this to work ATM. I'll give it another look soon.

 

[Gord]

"can't get it to work" means what?

Don't know where to put it or how to run it?

Data is not as you think it is?

Perhaps your times are simply formatted to appear as you showed.


Gord

 

[Saxman]

I have had another go at this and this time it works perfectly and I've
even assigned a macro to it.

Thank you so much. It really speed the process up.

The code below was written by Sandy Mann. He helped me a lot, but sadly
is no longer with us.

 

[Gord]

Good to hear you got it working.

Gord

I have had another go at this and this time it works perfectly and I've
even assigned a macro to it.

Thank you so much. It really speed the process up.

The code below was written by Sandy Mann. He helped me a lot, but sadly
is no longer with us.