Filling up the missing Dates (Excel 2010)

[mzalikhan]

Hi everybody

Here is my question. I have got "Daily" weather data for "Ten Years". So Column A is date, B, C, .......H are the different parameters. For ten years, its around 3650 rows.

Problem here is that some days are missing. For example after 5-August-2001 the next data entry is 17-August-2001, but there are no empty rows for the missing dates. I need to put the empty rows with the dates in Column A for the missing dates.

Peace
Muhammad Zeeshan

 

[WhoCanDo]

Try this mzalikhan:

Sub Fill_Out_Dates()
'
Cells(65000, 1).Select ' 1 is column A etc.
Selection.End(xlUp).Select

Do While (ActiveCell.Value <> "") And (ActiveCell.Row > 1)
If (ActiveCell.Value - 1 <> Cells(ActiveCell.Row - 1, 1).Value) Then
ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.Value = Cells(ActiveCell.Row + 1, 1).Value - 1
Else
Cells(ActiveCell.Row - 1, 1).Select
End If
Loop
End Sub