Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Clear Date Stamp

Posted on 2014-09-01
5
Medium Priority
?
185 Views
Last Modified: 2014-09-02
Hi,

I have a file where i am trying to remove the date stamp, it isnt working though, can someone have a look and amend

Many thanks

Sub RemoveDateStamps()

For Each c In Range(Range("A1"), Range("A" & Cells.Rows.Count).End(xlUp))
    If c < Date - 2 And c > 0 Then
        c.EntireRow.ClearContents
    End If
Next
End Sub
Book1.xlsx
0
Comment
Question by:Seamus2626
5 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40296827
I can only see one date in that column and it doesn't meet the criteria. What are you expecting to happen?
0
 

Author Comment

by:Seamus2626
ID: 40296839
I want the date stamp removed Rory, what is the criteria?
0
 

Author Comment

by:Seamus2626
ID: 40296855
So i want 01/09/2014 removed

Thanks
0
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 600 total points
ID: 40296978
In your VBA Script your criteria are:

Cell value is less than Date - 2 and Cell value is Greater than 0

Date is a standard variable in VBA for the value of today, so in this instance will be 1 Sept 2014. For dates, less than is the same as earlier than. Therefore the cell value is not less/earlier than Date - 2 so won't be removed.

Thanks
Rob H
0
 
LVL 24

Accepted Solution

by:
Ejgil Hedegaard earned 1400 total points
ID: 40296982
You could use IsDate to check for any date.

Sub RemoveDateStamps()
Dim c As Range
For Each c In Range(Range("A1"), Range("A" & Cells.Rows.Count).End(xlUp))
    If IsDate(c) Then
        c.EntireRow.ClearContents
    End If
Next
End Sub

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question