Automatically adding the date time to a cell when another cell is updated AND clearing it when this cell is empty

0 votes

I'm making an excel document for business reports. I took the script from this discussion in the MS Support -  LINK
It appears as follows:

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1")) Is Nothing Or _
    Not Intersect(Target, Range("B1")) Is Nothing Or _
    Not Intersect(Target, Range("C1")) Is Nothing Or _
    Not Intersect(Target, Range("D1")) Is Nothing Or _
    Not Intersect(Target, Range("E1")) Is Nothing Then

    Target.Offset(1, 0) = Now
 End If

End Sub

It functions perfectly, but now I want to clear target cells after clearing the main cells. Now, for instance, if I delete A1, the time in A2 remains. I'm really new at Excel and am hoping for some assistance.

5 days ago in Others by Kithuzzz
• 27,740 points
24 views

1 answer to this question.

0 votes

You are looking for an IsEmpty check in your code:

If IsEmpty(Target.Value) Then Target.Offset(1, 0).ClearContents Else Target.Offset(1, 0) = Now

As a whole:

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1:E1")) Is Nothing Then

    If IsEmpty(Target.Value) Then Target.Offset(1, 0).ClearContents Else Target.Offset(1, 0) = Now

 End If

End Sub
answered 5 days ago by narikkadan
• 51,240 points

Related Questions In Others

0 votes
0 answers

How is it possible for DynamoDB to support both Key-Value and Document database properties at the same time

As per DynamoDB's documentation, it supports both ...READ MORE

Apr 5, 2022 in Others by Kichu
• 19,040 points
256 views
0 votes
1 answer

When a row is added to one sheet, automatically add a row to another sheet

Use the VLOOKUP function. Your Spreadsheet 2's first column will ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 51,240 points
110 views
0 votes
1 answer

VBA How to extract the date and time of arrival of a answered email

Use MailItem.ReceivedTime property. I hope this helps you ...READ MORE

answered Jan 9 in Others by narikkadan
• 51,240 points
36 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9 in Others by narikkadan
• 51,240 points
32 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,640 points
165 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 51,240 points
254 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 51,240 points
171 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 51,240 points
255 views
0 votes
1 answer

Excel Formula which places date/time in cell when data is entered in another cell in the same row

Here's how to accomplish things in another ...READ MORE

answered Dec 11, 2022 in Others by narikkadan
• 51,240 points
63 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP