Need to split a data range into one row per day [VBA]

[Mattyastill]

Hi Everyone,

Looking for some assitance with a VBA macro that would split a date range into seperate rows for each day within the range.

I have several thousand rows of data which are holiday requests and the data consists of 9 rows (see image)

I need the new list to start in "Sheet 2" in cell "A1" where each day within the date range is an individual row. Obviously the new data would only contain "Date" instead of "Start Date" & "End Date"

Appreciate any help on this.

 

[Anthony47]

And which data should be copied in which column? Or you only need a sequence of dates created from each of the Start/EndDate? All the dates need to be listed, eg 12 lines in case that Start is Aug 16 and End is Aug 27?
Bye

 

[Mattyastill]

And which data should be copied in which column? Or you only need a sequence of dates created from each of the Start/EndDate? All the dates need to be listed, eg 12 lines in case that Start is Aug 16 and End is Aug 27?
Bye

For each employee ID i would need the number of rows that exist in the range Start Date: End Date.

For example Employe ID: 1000. Would have 3 rows where it would look like the below.

 

[Anthony47]

Try the following macro:

Sub Expand()
Dim I As Long, DeSh As Worksheet, J As Long
Dim jInd As Long
'
jInd = 1
Set DeSh = Sheets("Sheet2")             '<<< The destination Sheet
DeSh.Range("A:I").ClearContents
Cells(1, 1).Resize(1, 9).Copy DeSh.Range("A1")
For I = 2 To Cells(Rows.Count, "F").End(xlUp).Row
    For J = Cells(I, "E").Value To Cells(I, "F").Value
        jInd = jInd + 1
        DeSh.Cells(jInd, 1).Resize(1, 9).Value = Cells(I, 1).Resize(1, 9).Value
        DeSh.Cells(jInd, "E").Resize(1, 2).Value = J
    Next J
Next I
End Sub

Copy the code to a Standard Module of your vba-project; then select the worksheet with the starting datas and start Sub Expand

Note that Columns A:I of the destination sheet will be CLEARED WITHOUT ANY ADVICE before creating the new table

Bye

 

[lrobbo314]

Another way using Power Query.

ExpandIP.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Emp Id	First	Last	Location	Start	End	Duration	Status	Approved		Emp Id	First	Last	Location	Start Date	End Date	Duration	Status	Approved
2	1000	a	b	UK	8/16/2021	8/18/2021	22:30	Approved	x		1000	a	b	UK	8/16/2021	8/16/2021	22:30	Approved	x
3	1001	c	d	UK	8/16/2021	8/18/2021	15:00	Approved	x		1000	a	b	UK	8/17/2021	8/17/2021	22:30	Approved	x
4	1002	e	f	UK	7/12/2021	7/15/2021	22:30	Approved	x		1000	a	b	UK	8/18/2021	8/18/2021	22:30	Approved	x
5	1003	g	h	UK	7/16/2021	7/18/2021	15:00	Approved	x		1001	c	d	UK	8/16/2021	8/16/2021	15:00	Approved	x
6	1004	i	j	UK	8/1/2021	8/3/2021	22:30	Approved	x		1001	c	d	UK	8/17/2021	8/17/2021	15:00	Approved	x
7											1001	c	d	UK	8/18/2021	8/18/2021	15:00	Approved	x
8											1002	e	f	UK	7/12/2021	7/12/2021	22:30	Approved	x
9											1002	e	f	UK	7/13/2021	7/13/2021	22:30	Approved	x
10											1002	e	f	UK	7/14/2021	7/14/2021	22:30	Approved	x
11											1002	e	f	UK	7/15/2021	7/15/2021	22:30	Approved	x
12											1003	g	h	UK	7/16/2021	7/16/2021	15:00	Approved	x
13											1003	g	h	UK	7/17/2021	7/17/2021	15:00	Approved	x
14											1003	g	h	UK	7/18/2021	7/18/2021	15:00	Approved	x
15											1004	i	j	UK	8/1/2021	8/1/2021	22:30	Approved	x
16											1004	i	j	UK	8/2/2021	8/2/2021	22:30	Approved	x
17											1004	i	j	UK	8/3/2021	8/3/2021	22:30	Approved	x
Sheet9

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    toDate = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
    List = Table.AddColumn(toDate, "Start Date", each List.Dates([Start],Number.From([End]-[Start])+1,#duration(1,0,0,0))),
    Expand = Table.ExpandListColumn(List, "Start Date"),
    Dupe = Table.DuplicateColumn(Expand, "Start Date", "End Date"),
    RC = Table.RemoveColumns(Dupe,{"Start", "End"}),
    Reorder = Table.ReorderColumns(RC,{"Emp Id", "First", "Last", "Location", "Start Date", "End Date", "Duration", "Status", "Approved"}),
    Types = Table.TransformColumnTypes(Reorder,{{"Start Date", type date}, {"End Date", type date}, {"First", type text}, {"Last", type text}, {"Location", type text}, {"Emp Id", Int64.Type}, {"Duration", type time}, {"Status", type text}, {"Approved", type text}})
in
    Types

 

[Mattyastill]

Try the following macro:

Sub Expand()
Dim I As Long, DeSh As Worksheet, J As Long
Dim jInd As Long
'
jInd = 1
Set DeSh = Sheets("Sheet2")             '<<< The destination Sheet
DeSh.Range("A:I").ClearContents
Cells(1, 1).Resize(1, 9).Copy DeSh.Range("A1")
For I = 2 To Cells(Rows.Count, "F").End(xlUp).Row
    For J = Cells(I, "E").Value To Cells(I, "F").Value
        jInd = jInd + 1
        DeSh.Cells(jInd, 1).Resize(1, 9).Value = Cells(I, 1).Resize(1, 9).Value
        DeSh.Cells(jInd, "E").Resize(1, 2).Value = J
    Next J
Next I
End Sub

Copy the code to a Standard Module of your vba-project; then select the worksheet with the starting datas and start Sub Expand

Note that Columns A:I of the destination sheet will be CLEARED WITHOUT ANY ADVICE before creating the new table

Bye

That works perfectly! Thank you.

 

[Mattyastill]

Another way using Power Query.

ExpandIP.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Emp Id	First	Last	Location	Start	End	Duration	Status	Approved		Emp Id	First	Last	Location	Start Date	End Date	Duration	Status	Approved
2	1000	a	b	UK	8/16/2021	8/18/2021	22:30	Approved	x		1000	a	b	UK	8/16/2021	8/16/2021	22:30	Approved	x
3	1001	c	d	UK	8/16/2021	8/18/2021	15:00	Approved	x		1000	a	b	UK	8/17/2021	8/17/2021	22:30	Approved	x
4	1002	e	f	UK	7/12/2021	7/15/2021	22:30	Approved	x		1000	a	b	UK	8/18/2021	8/18/2021	22:30	Approved	x
5	1003	g	h	UK	7/16/2021	7/18/2021	15:00	Approved	x		1001	c	d	UK	8/16/2021	8/16/2021	15:00	Approved	x
6	1004	i	j	UK	8/1/2021	8/3/2021	22:30	Approved	x		1001	c	d	UK	8/17/2021	8/17/2021	15:00	Approved	x
7											1001	c	d	UK	8/18/2021	8/18/2021	15:00	Approved	x
8											1002	e	f	UK	7/12/2021	7/12/2021	22:30	Approved	x
9											1002	e	f	UK	7/13/2021	7/13/2021	22:30	Approved	x
10											1002	e	f	UK	7/14/2021	7/14/2021	22:30	Approved	x
11											1002	e	f	UK	7/15/2021	7/15/2021	22:30	Approved	x
12											1003	g	h	UK	7/16/2021	7/16/2021	15:00	Approved	x
13											1003	g	h	UK	7/17/2021	7/17/2021	15:00	Approved	x
14											1003	g	h	UK	7/18/2021	7/18/2021	15:00	Approved	x
15											1004	i	j	UK	8/1/2021	8/1/2021	22:30	Approved	x
16											1004	i	j	UK	8/2/2021	8/2/2021	22:30	Approved	x
17											1004	i	j	UK	8/3/2021	8/3/2021	22:30	Approved	x
Sheet9

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    toDate = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
    List = Table.AddColumn(toDate, "Start Date", each List.Dates([Start],Number.From([End]-[Start])+1,#duration(1,0,0,0))),
    Expand = Table.ExpandListColumn(List, "Start Date"),
    Dupe = Table.DuplicateColumn(Expand, "Start Date", "End Date"),
    RC = Table.RemoveColumns(Dupe,{"Start", "End"}),
    Reorder = Table.ReorderColumns(RC,{"Emp Id", "First", "Last", "Location", "Start Date", "End Date", "Duration", "Status", "Approved"}),
    Types = Table.TransformColumnTypes(Reorder,{{"Start Date", type date}, {"End Date", type date}, {"First", type text}, {"Last", type text}, {"Location", type text}, {"Emp Id", Int64.Type}, {"Duration", type time}, {"Status", type text}, {"Approved", type text}})
in
    Types

I never thought about using power query but i will explore this as an option and see which is a better fit. Thank you!

 

[Anthony47]

Thank you for the feedback

 

[Menegotc]

For each employee ID i would need the number of rows that exist in the range Start Date: End Date.

For example Employe ID: 1000. Would have 3 rows where it would look like the below.

View attachment 45212

Hi, I used this code for a similar query with success, I just have problem if the Start date and end date are the same the macro instead of giving me one line report 366 (the day + 365 additional days like following the whole year).

How can correct that?

Thank you in advanced guys.

 

[Anthony47]

Are you using my Sub Expand?

I tested the code and I had not the problem you report

Are you sure that columns E:F contains real "dates" (not strings); can you show your set or data (at least a picture)?