VBA Macro to fill in blank date cells with same preceding date?

[Excel Padwan]

Hello all,

I received an excel report that uses an unfortunate layout. Here's a sample:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]1/27/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 1[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 2[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 3[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 4[/TD]
[TD]$40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/27/2014 Total:[/TD]
[TD][/TD]
[TD]$190[/TD]
[/TR]
[TR]
[TD]1/28/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 1[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 2[/TD]
[TD]$40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 3[/TD]
[TD]$70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 4[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/28/2014 Total:[/TD]
[TD][/TD]
[TD]$260[/TD]
[/TR]
[TR]
[TD]1/29/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 1[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 2[/TD]
[TD]$70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 3[/TD]
[TD]$70[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 4[/TD]
[TD]$60[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 5[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Item 6[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1/29/2014 Total:[/TD]
[TD][/TD]
[TD]$280[/TD]
[/TR]
</tbody>[/TABLE]

Ideally, I would like it to look like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]1/27/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/27/2014[/TD]
[TD][/TD]
[TD]Item 1[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]1/27/2014[/TD]
[TD][/TD]
[TD]Item 2[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD]1/27/2014[/TD]
[TD][/TD]
[TD]Item 3[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]1/27/2014[/TD]
[TD][/TD]
[TD]Item 4[/TD]
[TD]$40[/TD]
[/TR]
[TR]
[TD]1/27/2014[/TD]
[TD]1/27/2014 Total:[/TD]
[TD][/TD]
[TD]$190[/TD]
[/TR]
[TR]
[TD]1/28/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/28/2014[/TD]
[TD][/TD]
[TD]Item 1[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]1/28/2014[/TD]
[TD][/TD]
[TD]Item 2[/TD]
[TD]$40[/TD]
[/TR]
[TR]
[TD]1/28/2014[/TD]
[TD][/TD]
[TD]Item 3[/TD]
[TD]$70[/TD]
[/TR]
[TR]
[TD]1/28/2014[/TD]
[TD][/TD]
[TD]Item 4[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]1/28/2014[/TD]
[TD]1/28/2014 Total:[/TD]
[TD][/TD]
[TD]$260[/TD]
[/TR]
[TR]
[TD]1/29/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/29/2014[/TD]
[TD][/TD]
[TD]Item 1[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD]1/29/2014[/TD]
[TD][/TD]
[TD]Item 2[/TD]
[TD]$70[/TD]
[/TR]
[TR]
[TD]1/29/2014[/TD]
[TD][/TD]
[TD]Item 3[/TD]
[TD]$70[/TD]
[/TR]
[TR]
[TD]1/29/2014[/TD]
[TD][/TD]
[TD]Item 4[/TD]
[TD]$60[/TD]
[/TR]
[TR]
[TD]1/29/2014[/TD]
[TD][/TD]
[TD]Item 5[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]1/29/2014[/TD]
[TD][/TD]
[TD]Item 6[/TD]
[TD]$10[/TD]
[/TR]
[TR]
[TD]1/29/2014[/TD]
[TD]1/29/2014 Total:[/TD]
[TD][/TD]
[TD]$280[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to simplify autofilling dates this way? Please note, I do not want to autofill a series. Instead, I'm looking for a way to autofill all the blanks cells between two values so that the blank cells copy the first date.

I am using Excel for Mac 2011.

Any help is much appreciated! Thank you!

 

[C Moore]

This should work (assuming it was column A with the dates in it).

Sub fill_dates()
    lastrow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Range(Range("A1"), Range("A" & lastrow)).Select
    Dim oRng As Range
    Set oRng = Selection
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.FormulaR1C1 = "=R[-1]C"
        oRng.Copy
        oRng.PasteSpecial Paste:=xlValues
End Sub

 

[Excel Padwan]

Woohoo! That did it. Thank you very much - a time saver!