insert blank line at change of year

[BillyRogers]

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?

[Tom Ogilvy]

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" <BillyRogers@discussions.microsoft.com> wrote in message
news:A11A7FF6-81A7-4D8B-8713-6B48ECD5B2CA@microsoft.com...
> 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?

[ben]

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?

[BillyRogers]

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.

[BillyRogers]

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.