Extracting First and Last Date from a data extract, and filling cells with all dates between them

[Jaevwyn]

Hi Guys,

I am trying to make a template report which can take raw data from a certain program and display it in a much easier to read format. For the most part I have been successful, however I am trying to remove as many manual components from this as possible oh which two remain.
My focus here is that when bringing in the raw data into excel, Column H of this data will have various time stamps formatted "dd/mm/yyyy hh:mm". Is there a way to have excel read these dates, pick out the earliest and latest of them, and populate all dates including and inbetween these dates on a different sheet formatted as dd/mm/yyyy in, for arguments sake, sheet 2 column A.

As as example, my raw data could contain in column H:

11/12/2017 10:17
13/12/2017 17:19
15/12/2017 07:10

And I would like to see on sheet 2 column A

11/12/2017
12/12/2017
13/12/2017
14/12/2017
15/12/2017


It may be a bit of a stretch both for Excel and certainly myself, I am not an experienced user and have never needed to use VBA thus far.

Many Thanks!

 

[Jaevwyn]

As an after thought, I would like to note the dates may not always be in order!

 

[wideboydixon]

Public Sub wbd2017121303()

Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim firstDate As Date
Dim lastDate As Date
Dim thisRow As Long

Set sourceSheet = Sheets("Sheet1") ' Sheet with dates in column H
Set targetSheet = Sheets("Sheet2") ' Sheet to put the new dates

' Find the last row on the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "H").End(xlUp).Row

' Get the first and last dates from column H
firstDate = Int(Application.WorksheetFunction.Min(sourceSheet.Range("H1:H" & lastRow)))
lastDate = Int(Application.WorksheetFunction.Max(sourceSheet.Range("H1:H" & lastRow)))

' Populate the dates onto the other sheet
thisRow = 1
Do While firstDate <= lastDate
    targetSheet.Cells(thisRow, "A").Value = firstDate
    firstDate = firstDate + 1
    thisRow = thisRow + 1
Loop

End Sub

WBD

 

[Jaevwyn]

Wow, thanks for the fast reply!
Get ready for the stupid question though...

Where do i input this? I have never used VBA

 

[Jaevwyn]

Nevermind I found it, but its disabled on our laptops here by default, will get that resolved and inputted!
Thanks