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

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" wrote:

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

On Jan 11, 6:37*pm, via135 wrote:
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

On Jan 11, 7:17*pm, via135 wrote:
On Jan 11, 6:37*pm, via135 wrote:



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

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

-via135

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

"via135" wrote in message
...
On Jan 11, 7:17 pm, via135 wrote:
On Jan 11, 6:37 pm, via135 wrote:



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

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

-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

"via135" wrote:

On Jan 11, 7:17 pm, via135 wrote:
On Jan 11, 6:37 pm, via135 wrote:



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

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

-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.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
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

"via135" wrote:

On Jan 11, 7:17 pm, via135 wrote:
On Jan 11, 6:37 pm, via135 wrote:



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

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

-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.

--
Rick (MVP - Excel)


"Bob Phillips" wrote in message
...
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

"via135" wrote in message
...
On Jan 11, 7:17 pm, via135 wrote:
On Jan 11, 6:37 pm, via135 wrote:



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

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

-via135

On Jan 11, 7:45*pm, Mike H wrote:
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

"via135" wrote:
On Jan 11, 7:17 pm, via135 wrote:
On Jan 11, 6:37 pm, via135 wrote:

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

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

-via135

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

-via135

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" wrote:

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 (MVP - Excel)


"Mike H" wrote in message
...
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

"via135" wrote:

On Jan 11, 7:17 pm, via135 wrote:
On Jan 11, 6:37 pm, via135 wrote:



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

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

-via135

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 (MVP - Excel)


"Mike H" wrote in message
...
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" wrote:

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 (MVP - Excel)


"Mike H" wrote in message
...
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

"via135" wrote:

On Jan 11, 7:17 pm, via135 wrote:
On Jan 11, 6:37 pm, via135 wrote:



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

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

-via135

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Bob Phillips" wrote in message
...
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

"via135" wrote in message
...
On Jan 11, 7:17 pm, via135 wrote:
On Jan 11, 6:37 pm, via135 wrote:



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

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

-via135

On Jan 11, 8:02*pm, "Rick Rothstein"
wrote:
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 (MVP - Excel)

"Bob Phillips" wrote in message

...

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

"via135" wrote in message
....
On Jan 11, 7:17 pm, via135 wrote:
On Jan 11, 6:37 pm, via135 wrote:

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

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

-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'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