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

**lesoies** · 09-22-2012, 07:42 AM

Hello,

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!

**Jakobshavn** · 09-22-2012, 08:23 AM

Try the small worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.WorksheetFunction.CountA(Target.Offset(-1, 0).EntireRow) = 0 Then
    Application.EnableEvents = False
    Cells(Target.Row - 1, "A").Value = Date
    Application.EnableEvents = True
End If
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

**lesoies** · 09-22-2012, 08:53 AM

Great, thank you very much!

One little thing though, I realised that I was inaccurate in my description of my problem.
Actually, the row is not entirely empty, coloumn "I" is populated either with a number or with a sign that basically states that it is empty.
How could I modify your ".EntireRow" to reflect that situation?

Thank you very, very much for your fast response!

**Jakobshavn** · 09-22-2012, 09:22 AM

Remove the first macro and insert:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wf As WorksheetFunction
Dim R1 As Range, R2 As Range
Set wf = Application.WorksheetFunction
Set R1 = Target.Offset(-1, 0).EntireRow
Set R2 = Cells(R1.Row, "I")
If wf.CountA(R1) = 1 And wf.CountA(R2) = 1 Then
    Application.EnableEvents = False
    Cells(Target.Row - 1, "A").Value = Date
    Application.EnableEvents = True
End If
End Sub

**lesoies** · 09-22-2012, 09:28 AM

Great, that works, thank you very much!

**Cutter** · 09-22-2012, 11:44 AM

@ lesoies

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.