CODEWITHSUNDEEP
dateexcelexcel-2007autofillvba
replax
replax

Reputation: 307

Auto-fill the current date if the previous row was empty

I have a spreadsheet sorted by dates. Everyday, I enter new data into it. After each day, I leave one row empty before starting the next day. Example:

20.09.12 XXXXX XXXXX XXXXX XXXX
         XXXXX XXXX  XXXXX XX
         XX    XXXXX XX    XXXX

21.09.12 XXX   XXXXX XXX   XXXX
         X     XXXX  XXXX  XXX

22.09.12 XXXX  XX    XXXX  XXXX

So I want excel to always fill in the current date whenever I enter data after one blank row, but obviously the dates shall stay the same - not be updated to the current date whenever I open excel. I am using Excel 2007 and while non vba ideas are preferred, vba is not a problem either!

Upvotes: 0

Views: 2128

Answers (2)

Alex P
Alex P

Reputation: 12487

Here is a fairly simple (crude?) way of doing it with VBA using the Worksheet_Change event of a worksheet.

Effectively, each time you change a cell it checks to see if the entire row above is blank and, if so, puts today's date in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    If WorksheetFunction.CountA(Rows(Target.Offset(-1, 0).Row)) = 0 Then
        Range("A" & Target.Row) = Format(Now(), "dd.mm.yy")
    End If
End Sub

Upvotes: 1

nutsch
nutsch

Reputation: 5962

There is a shortcut for inserting today's date: Ctrl;
I think if you use the shortcut, you probably don't need a macro.

Similar shortcut for time is CtrlShift;

Upvotes: 3

Related Questions