Macro using for loop to repeat rows based on from and to date columns

[haribabu]

Hello Excel users,
i am trying to create a macro where i can repeat rows based on from and to date columns.
So is there any way using for loop which can get the below desired output in csv by using macro?



INPUT from excel sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]From[/TD]
[TD]To[/TD]
[TD]Product[/TD]
[TD]Growth[/TD]
[/TR]
[TR]
[TD]19-09-2018[/TD]
[TD]25-09-2018[/TD]
[TD]A[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]23-09-2018[/TD]
[TD]27-09-2018[/TD]
[TD]B[/TD]
[TD]3%[/TD]
[/TR]
</tbody>[/TABLE]

Expected Output in CSV
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]Growth[/TD]
[/TR]
[TR]
[TD]19-09-2018[/TD]
[TD]A[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]20-09-2018[/TD]
[TD]A[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]21-09-2018[/TD]
[TD]A[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]22-09-2018[/TD]
[TD]A[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]23-09-2018[/TD]
[TD]A[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]24-09-2018[/TD]
[TD]A[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]25-09-2018[/TD]
[TD]A[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]23-09-2018[/TD]
[TD]B[/TD]
[TD]3%[/TD]
[/TR]
[TR]
[TD]24-09-2018[/TD]
[TD]B[/TD]
[TD]3%[/TD]
[/TR]
[TR]
[TD]25-09-2018[/TD]
[TD]B[/TD]
[TD]3%[/TD]
[/TR]
[TR]
[TD]26-09-2018[/TD]
[TD]B[/TD]
[TD]3%[/TD]
[/TR]
[TR]
[TD]27-09-2018[/TD]
[TD]B[/TD]
[TD]3%[/TD]
[/TR]
</tbody>[/TABLE]


Thanks a lot

 

[John_w]

Try this macro:

Public Sub Create_CSV_File()

    Dim csvFile As Variant
    Dim csvData As String
    Dim lastRow As Long, r As Long
    Dim d As Date
    
    csvFile = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.Path, FileFilter:="CSV file (*.csv), *.csv", Title:="CSV output file")
 
    If csvFile <> False Then
        With ActiveSheet
            lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            csvData = "Date,Product,Growth" & vbCrLf
            For r = 2 To lastRow
                For d = .Cells(r, "A").Value To .Cells(r, "B").Value
                    csvData = csvData & Format(d, "dd-mm-yyyy") & "," & .Cells(r, "C").Value & "," & .Cells(r, "D").Text & vbCrLf
                Next
            Next
        End With
        Open csvFile For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , csvData
        Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    End If
    
End Sub

 

[haribabu]

Thanks a lot John
It works like a charm