Macros In Excel

Ribs7774

New Member
Joined
Jan 2, 2014
Messages
6
Hi,

I need a marco that can check if a date & time entered is over 48Hours & if it is to delete the data in this row.

Example:
Time Set for entry is = 02/01/2014 12.00

After 48 Hours has expired > 04/01/2014 12.01

This row is deleted.

If the entry has not expired the row is to remain until it has expired.

There maybe up to a couple hundred entries, so manually checking is not ideal, but I believe a macro can do the trick just not sure how to setup the marco script.

Thanks in advance for your assistance :)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
You don't really need a macro for this. You could simply add a new column using a formula like:
=now()-A2
(where A2 is the date/time cell) and then use an autofilter to only show rows greater than 2 and then delete them.
 
Upvote 0
Hi Rory,

thank you for the idea but this way will require manual checking everyday.

I really need this to be done automatically without having to manually delete the expired items.
 
Upvote 0
What exactly do you mean by 'automatic'? How should the macro be triggered?
 
Upvote 0
Sorry I know the macro needs to be triggered, but by automatic I meant not having to manually check and delete expired items
 
Upvote 0
OK - can you give us an idea of the data layout?
 
Upvote 0
Please find below layout example

Cords</SPAN>Time added</SPAN>Name</SPAN>
123,456</SPAN>6/01/2014 12:11</SPAN>Ribs7774</SPAN>
157,486</SPAN>6/01/2014 13:43</SPAN>Ribs7774</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>
 
Upvote 0
Try something like this:
Code:
Sub DeleteOldData()
    Dim CutoffDate As Date
    ' you can populate this from a cell if required.
    CutoffDate = Now()
    
    With ActiveSheet
        .AutoFilterMode = False
        With .Range("A1").CurrentRegion
            ' filters on second column for dates more than 48 hours before the specified date
            .AutoFilter field:=2, Criteria1:="<" & CDbl(CutoffDate - 2)
            If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
        End With
        .AutoFilterMode = False
    End With
        
End Sub
 
Upvote 0
Glad to help.

Welcome to the forum, by the way. :)
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top