Hi all,
Given a list of dates with different times. Would it be able to check the list of dates, sort it in ascending order and check whether there is any missing date, if there is missing date can it be able to insert the date rather than a blank row. The example is shown below.. ( The date will be repeating)
EXAMPLE
BEFORE
row 1 13/6/2005 12:01 --0.1
row 2 14/6/2005 14:08 --0.5
row 3 16/6/2005 08:65 --0.12
row 4 16/6/2005 11:46 --0.31
AFTER
row 1 13/6/2005 12:01 --0.1
row 2 14/6/2005 14:08 --0.5
row 3 15/6/2005 00:00 --
row 4 16/6/2005 08:65 --0.12
row 5 16/6/2005 11:46 --0.31
This code can only work with NO REPEATING date
Given a list of dates with different times. Would it be able to check the list of dates, sort it in ascending order and check whether there is any missing date, if there is missing date can it be able to insert the date rather than a blank row. The example is shown below.. ( The date will be repeating)
EXAMPLE
BEFORE
row 1 13/6/2005 12:01 --0.1
row 2 14/6/2005 14:08 --0.5
row 3 16/6/2005 08:65 --0.12
row 4 16/6/2005 11:46 --0.31
AFTER
row 1 13/6/2005 12:01 --0.1
row 2 14/6/2005 14:08 --0.5
row 3 15/6/2005 00:00 --
row 4 16/6/2005 08:65 --0.12
row 5 16/6/2005 11:46 --0.31
Code:
Sub SortCompleteDate()
Dim lRow As Long, lRowMax As Long
Application.ScreenUpdating = False
Range("A1", Cells(Rows.Count, "A").End(xlUp)).sort key1:=Range("A1"), order1:=xlAscending, header:=xlNo
lRowMax = Cells(Rows.Count, 1).End(xlUp).Row
For lRow = lRowMax To 2 Step -1
Do While Int(Cells(lRow, "A")) <> Int(Cells(lRow, "A").Offset(-1)) + 1
Cells(lRow, "A").Resize(, 2).Insert
Cells(lRow, "A") = Int(Cells(lRow, "A").Offset(1)) - 1
Loop
Next
Application.ScreenUpdating = True
End Sub
This code can only work with NO REPEATING date