inserting rows for month end dates.!

  • Thread starter Thread starter via135
  • Start date Start date
V

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
 
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
 
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
Click to expand...

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
 
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
Click to expand...

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

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
Click to expand...

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
 
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.
 
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
Click to expand...

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
 
The OP appears happy with your and Bob's code, so it looks like you were
right and I was wrong in my interpretation.
 
The OP appears happy with your and Mike's code, so it looks like you were
right and I was wrong in my interpretation.
 
I think you are getting an extra row between June 30th and July 20th... June
30th is already the last day of the month.
Click to expand...

***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
 
Back
Top