insert blank line at change of year
 

I have a spreadsheet with a column of sorted dates like this

27-Feb-01
27-Feb-01
01-Feb-02
01-Feb-02
01-Feb-02

I would like a macro that can insert a row at each change of year. Does
anyone have any ideas how to do this?

insert blank line at change of year
 

Assume first date is in A2; dates in column A.

lastrow = cells(rows.count,1).End(xlup).Row
for i = lastrow to 3 Step -1
if Year(cells(i,1)) < Year(cells(i-1,1)) then
rows(i).Insert
end if
Next

--
Regards,
Tom Ogilvy


"BillyRogers" wrote in message
...
I have a spreadsheet with a column of sorted dates like this

27-Feb-01
27-Feb-01
01-Feb-02
01-Feb-02
01-Feb-02

I would like a macro that can insert a row at each change of year. Does
anyone have any ideas how to do this?

insert blank line at change of year
 

this assumes all dates are in Column 1



Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub



--
When you lose your mind, you free your life.
Ever Notice how we use '' for comments in our posts even if they aren''t
expected to go into the code?


"BillyRogers" wrote:

I have a spreadsheet with a column of sorted dates like this

27-Feb-01
27-Feb-01
01-Feb-02
01-Feb-02
01-Feb-02

I would like a macro that can insert a row at each change of year. Does
anyone have any ideas how to do this?

insert blank line at change of year
 

Thank guys both of those worked great. I would also like to insert in the
blank cell above each change in year "10th Anniversary" if the dates were
ten years ago, "9th anniversary"(in bold) etc.

insert blank line at change of year
 

Ben,

I just discovered a flaw in your code. you start out looping using the
rowcount from the top, but everytime you add a space it pushes the last row
further away so your original row count is not high enought to cycle throught
all the rows it needs to.