Add rows and customized the date

[sanj_edu]

The actual data is below:

S. No	First Name	Last Name	Gen ID	Band	Start Date	End Date
1	Aaron	Small	33371	S1	20-01-2017	14-12-2017
2	Abdul	Karenina	33372	S2	25-10-2017	28-11-2017
3	Abe	Sarandon	33373	S3	18-05-2017	20-07-2017
4	Abraham	D'Arby	33375	S5	15-03-2017	20-05-2017
5	Abram	Palyuvchenko	33376	S6	08-01-2017	09-01-2017
6	sarftyu	Sarandon	33373	S3	21-05-2017	20-07-2017

<tbody>
</tbody><colgroup><col><col span="6"></colgroup>

the End Result should be like:

S. No	First Name	Last Name	Gen ID	Band	Start Date	End Date
1	Aaron	Small	33371	S1	20-01-2017	31-01-2017
1	Aaron	Small	33371	S1	01-02-2017	28-02-2017
1	Aaron	Small	33371	S1	01-03-2017	31-03-2017
1	Aaron	Small	33371	S1	01-04-2017	30-04-2017
1	Aaron	Small	33371	S1	01-05-2017	31-05-2017
1	Aaron	Small	33371	S1	01-06-2017	30-06-2017
1	Aaron	Small	33371	S1	01-07-2017	31-07-2017
1	Aaron	Small	33371	S1	01-08-2017	31-08-2017
1	Aaron	Small	33371	S1	01-09-2017	30-09-2017
1	Aaron	Small	33371	S1	01-10-2017	31-10-2017
1	Aaron	Small	33371	S1	01-11-2017	30-11-2017
1	Aaron	Small	33371	S1	01-12-2017	14-12-2017
2	Abdul	Karenina	33372	S2	25-10-2017	28-11-2017
2	Abdul	Karenina	33372	S2	25-10-2017	31-10-2017
2	Abdul	Karenina	33372	S2	01-11-2017	28-11-2017

<tbody>
</tbody><colgroup><col><col span="6"></colgroup>

 

[Joe4]

Welcome to the Board!

Assuming that your data is in columns A-G, your header is in row 1, and your data begins on row 2, this VBA code should do what you want:

Sub MyInsertMacro()

    Dim myRow As Long
    Dim stDate As Date
    Dim endDate As Date
    Dim eomDate As Date
        
    Application.ScreenUpdating = False

'   Enter first row of data    
    myRow = 2

'   Loop until column A is blank
    Do Until Cells(myRow, "A") = ""
'       Get dates from row
        stDate = Cells(myRow, "F")
        endDate = Cells(myRow, "G")
'       Calculate end of month date from initial start date
        eomDate = DateSerial(Year(stDate), Month(stDate) + 1, 0)
'       Check to see if end date is greater than end of month date
        If endDate > eomDate Then
'           Insert new row
            Rows(myRow + 1).Insert
'           Copy first five columns
            Range(Cells(myRow, "A"), Cells(myRow, "E")).Copy Cells(myRow + 1, "A")
'           Update end date on first record
            Cells(myRow, "G") = eomDate
'           Update dates on new row
            Cells(myRow + 1, "F") = eomDate + 1
            Cells(myRow + 1, "G") = endDate
        End If
'       Increment row counter
        myRow = myRow + 1
    Loop

    Application.ScreenUpdating = True
    
End Sub

 

[sanj_edu]

Hi,

Joe4, It works perfectly fine. You are really the Best.

Thanks a lot for such a quick response!!.


Best Forum to discuss!!!

Welcome to the Board!

Assuming that your data is in columns A-G, your header is in row 1, and your data begins on row 2, this VBA code should do what you want:

Sub MyInsertMacro()

    Dim myRow As Long
    Dim stDate As Date
    Dim endDate As Date
    Dim eomDate As Date
        
    Application.ScreenUpdating = False

'   Enter first row of data    
    myRow = 2

'   Loop until column A is blank
    Do Until Cells(myRow, "A") = ""
'       Get dates from row
        stDate = Cells(myRow, "F")
        endDate = Cells(myRow, "G")
'       Calculate end of month date from initial start date
        eomDate = DateSerial(Year(stDate), Month(stDate) + 1, 0)
'       Check to see if end date is greater than end of month date
        If endDate > eomDate Then
'           Insert new row
            Rows(myRow + 1).Insert
'           Copy first five columns
            Range(Cells(myRow, "A"), Cells(myRow, "E")).Copy Cells(myRow + 1, "A")
'           Update end date on first record
            Cells(myRow, "G") = eomDate
'           Update dates on new row
            Cells(myRow + 1, "F") = eomDate + 1
            Cells(myRow + 1, "G") = endDate
        End If
'       Increment row counter
        myRow = myRow + 1
    Loop

    Application.ScreenUpdating = True
    
End Sub

 

[Joe4]

You are welcome!

I tried to add lots of comments to the code to explain what each step is doing.
Hopefully, it all makes sense.