PDA

View Full Version : [SOLVED] How to extract date range into multiple rows?



pivotguy
05-17-2016, 04:35 PM
I have a input file which have date range for 5,10,15 days. I would like to extract those date range into each single rows.
See example below and attached INPUT.xls file . Can you guide me to write a VBA code. The original file contains more than thousand records .


Input File
=======

ID------ from date----- TO Date
----------------------------

1020---- 2/20/2016 ---- 2/22/2016



Output File
=======

ID----- from date----- TO Date
---------------------------
1020------2/20/2016---- 2/20/2016
1020----- 2/21/2016---- 2/21/2016
1020----- 2/22/2016----- 2/22/2016

skywriter
05-17-2016, 06:52 PM
Try this.


Sub PivotGuy()
Dim arr1, arr2, c As Long, x As Long, y As Long
With Worksheets("INPUT").Range("A1").CurrentRegion
arr1 = .Offset(1).Resize(.Rows.Count - 1, 5).Value
ReDim arr2(1 To WorksheetFunction.Sum(.Columns(4)), 1 To 5)
End With
y = 1
For c = LBound(arr1, 1) To UBound(arr1, 1)
For x = 1 To arr1(c, 4)
arr2(y, 1) = arr1(c, 1): arr2(y, 2) = arr1(c, 2) + x - 1: arr2(y, 3) = arr1(c, 2) + x - 1
arr2(y, 4) = 1: arr2(y, 5) = arr1(c, 5): y = y + 1
Next x
Next c
Worksheets("OUTPUT").Range("A2").Resize(UBound(arr2, 1), UBound(arr2, 2)).Value = arr2
End Sub

pivotguy
05-17-2016, 08:22 PM
Skywriter: Thanks you very much for your time and effort . It worked with no issue. I really appreciate your time. You rock.

skywriter
05-17-2016, 09:49 PM
My pleasure, thanks for the feedback.
:beerchug:

dc1z
10-26-2016, 07:36 AM
Hi, Just to thank you again, this is exactly what I needed as well, many thanks