insert rows in list

[Evelyn]

Column A is lists each week beginning in 6-Jan-01 until the present. I would
like to create something that automatically inserts a row after every month
since the file has 400+ rows and each month varies from 4-5 weeks.

 

[Mike H]

Evelyn,

I don't understand.
Is column A simply a list of dates?
A month is 28, 29, 30 or 31 days long and not 4 or 5 weeks where do you want
the line inserting?
If the list is from 6/1/01 until today I make that > 3000 rows. Please
clarify.

Mike

 

[Evelyn]

Hi Mike,

Column A is listed below but begins in Jan 2001 and goes until the current
week. I want to be able to have rows inserted after every month so a row
would automatically be inserted after the 4 rows of Jan, then the 4 rows of
Feb, 5 rows of Mar, etc...

6-Jan-01
13-Jan-01
20-Jan-01
27-Jan-01
3-Feb-01
10-Feb-01
17-Feb-01
24-Feb-01
3-Mar-01
10-Mar-01
17-Mar-01
24-Mar-01
31-Mar-01

 

[Mike H]

Evelyn

Right click your sheet tab, view code and paste this in and run it

Sub sonic()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 2 Step -1
If Month(Cells(x, 1)) <> Month(Cells(x - 1, 1)) Then
Rows(x).EntireRow.Insert
End If
Next
End Sub

Mike

 

[Evelyn]

Wow, that worked like a charm. Thanks for your help! Mike's help = awesome.