inserting rows for month end dates.!

[via135]

hi all

i am having a list of dates "dd/mm/yyyy" format in col A as under::

1/1/2009
5/1/2009
10/2/2009
15/6/2009
30/6/2009
20/7/2009
10/9/2009
1/1/2010

what i want is to inset row for each month end dates.upto the end of
data range..?!
any hlp?

-via135

 

[Mike H]

Hi,

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

Sub stantial()
Dim lastrow As Long
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

 

[via135]

hi all

i am having a list of dates "dd/mm/yyyy" format in col A as under::

1/1/2009
5/1/2009
10/2/2009
15/6/2009
30/6/2009
20/7/2009
10/9/2009
1/1/2010

what i want is to inset row for each month end dates.upto the end of
data range..?!
any hlp?

-via135

yes..i am getting the rows inserted only for the months available in
the data.
but i need rows tobe inserted for all months which are not figured in
the data also.
for instance.. i need 3 rows tobe inserted btw
10/2/2009 &
15/6/2009

hope u understand Mike..!

-via135

 

[via135]

yes..i am getting the rows inserted only for the months available in
the data.
but i need rows tobe inserted for all months which are not figured in
the data also.
for instance.. i need 3 rows tobe inserted btw
10/2/2009 &
15/6/2009

hope u understand Mike..!

-via135

oops..indeed 4 rows for feb,mar,apr & may

-via135

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim nMonths As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow - 1 To 2 Step -1

nMonths = DateDiff("m", .Cells(i, TEST_COLUMN).Value, .Cells(i +
1, TEST_COLUMN).Value)
If nMonths > 0 Then .Rows(i + 1).Resize(nMonths).Insert
Next i

End With

End Sub


--
__________________________________
HTH

Bob

yes..i am getting the rows inserted only for the months available in
the data.
but i need rows tobe inserted for all months which are not figured in
the data also.
for instance.. i need 3 rows tobe inserted btw
10/2/2009 &
15/6/2009

hope u understand Mike..!

-via135

oops..indeed 4 rows for feb,mar,apr & may

-via135

 

[Mike H]

Hi,

Try this instead. Same installation as previous code

Sub stantial()
Dim lastrow As Long
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
For a = 1 To DateDiff("m", Cells(x - 1, 1), Cells(x, 1))
Rows(x).EntireRow.Insert
Next
End If
Next
End Sub

Mike

 

[Rick Rothstein]

I think you are getting an extra row between June 30th and July 20th... June
30th is already the last day of the month.

 

[Rick Rothstein]

I think you are getting an extra row between June 30th and July 20th... June
30th is already the last day of the month.

 

[via135]

Hi,

Try this instead. Same installation as previous code

Sub stantial()
Dim lastrow As Long
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
For a = 1 To DateDiff("m", Cells(x - 1, 1), Cells(x, 1))
Rows(x).EntireRow.Insert
Next
End If
Next
End Sub

Mike

yest it is..!
thks Mike & Bob..!
both the codes are doing the trick..!
thks again both of u..!

-via135

 

[Mike H]

Thanks Rick,

It's doing what I want it to do but whether that's what the OP wants is
another matter. My understanding is that the day isn't relevant and the idea
is to make a space for intermediate months.

It's an easy enough fix if that's what the OP wants but the questions is
vague so I'll see if we get some feedback.

Mike

 

[Rick Rothstein]

The OP appears happy with your and Bob's code, so it looks like you were
right and I was wrong in my interpretation.

 

[Rick Rothstein]

The OP appears happy with your and Mike's code, so it looks like you were
right and I was wrong in my interpretation.

 

[via135]

I think you are getting an extra row between June 30th and July 20th... June
30th is already the last day of the month.

***I think you are getting an extra row between June 30th and July
20th... June
30th is already the last day of the month. ***

i've given that 30th Jun for meaningful purpose only.!
now i am telling clearly..
the purpose is to apply interest portion on the last day of every
month.
there may be another transaction on the last day other than interest
application..!
that's why i got full satisfaction with the codes!
hope u all three catch my point..!

-via135