--------------------------------------------------------------------------------
Given a list of dates. 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..
EXAMPLE
BEFORE
row 1 13/6/2005 0.1
row 2 14/6/2005 0.5
row 3 16/6/2005 0.12
row 4 17/6/2005 0.31
AFTER
row 1 13/6/2005 0.1
row 2 14/6/2005 0.5
row 3 15/6/2005
row 4 16/6/2005 0.12
row 5 17/6/2005 0.31
Given a list of dates. 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..
EXAMPLE
BEFORE
row 1 13/6/2005 0.1
row 2 14/6/2005 0.5
row 3 16/6/2005 0.12
row 4 17/6/2005 0.31
AFTER
row 1 13/6/2005 0.1
row 2 14/6/2005 0.5
row 3 15/6/2005
row 4 16/6/2005 0.12
row 5 17/6/2005 0.31
Code:
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row).Sort key1:=Range("B2"), order1:=xlAscending, header:=xlNo
Columns("B:B").TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
Range("E3").Select
While Not IsEmpty(ActiveCell)
If ActiveCell <> ActiveCell.Offset(-1, 0) + 1 And ActiveCell <> ActiveCell.Offset(-1, 0) Then
t = ActiveCell - ActiveCell.Offset(-1, 0)
ActiveCell.Resize(t - 1).EntireRow.Insert
ActiveCell.Offset(t, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Wend
Columns("E:G").Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = True