Create a Macro to Delete All Rows that meet a certain criteria

[jpittari]

I have a security log which shows each date/time a user is admitted and then
enters through a secure door in our office suite using their security badge.
I need a macro that will remove all the rows in the spreadsheet exept the
earliest access time and the latest access time for each date.

Ultimately I need to determine how long a user was in the office suite each
day, assuming that the last card swipe was made when the user left the office
at the end of the day.

Here is some example data:

Admitted 10/17/2007 12:23
Entered 10/17/2007 12:23
Admitted 10/17/2007 14:37
Entered 10/17/2007 14:37
Admitted 10/17/2007 16:55
Entered 10/17/2007 16:55
Admitted 10/17/2007 18:31
Admitted 10/18/2007 10:13
Entered 10/18/2007 10:13
Admitted 10/18/2007 14:11
Entered 10/18/2007 14:11
Admitted 10/18/2007 17:39
Entered 10/18/2007 17:39
Admitted 10/18/2007 17:43
Entered 10/18/2007 17:43
Admitted 10/19/2007 10:46
Entered 10/19/2007 10:46
Admitted 10/19/2007 16:39
Entered 10/19/2007 16:39
Admitted 10/19/2007 18:40
Entered 10/19/2007 18:40
Admitted 10/19/2007 21:13
Admitted 10/22/2007 10:33
Entered 10/22/2007 10:33
Admitted 10/22/2007 13:17
Entered 10/22/2007 13:17
Admitted 10/22/2007 14:03
Entered 10/22/2007 14:03
Admitted 10/22/2007 15:58
Entered 10/22/2007 15:58
Admitted 10/22/2007 18:51


Desired Result:

Admitted 10/17/2007 12:23
Admitted 10/17/2007 18:31
Admitted 10/18/2007 10:13
Entered 10/18/2007 17:43 (users do not always swipe card before leaving)
Admitted 10/19/2007 10:46
Admitted 10/19/2007 21:13
Admitted 10/22/2007 10:33
Admitted 10/22/2007 18:51

 

[Bill Renaud]

A quick, non-macro method to solve this would be to insert a row above row
1 and enter column labels to make your data look as follows:

Action Date Time Delete
Admitted 10/17/2007 12:23
Entered 10/17/2007 12:23
Admitted 10/17/2007 14:37
Entered 10/17/2007 14:37
Admitted 10/17/2007 16:55
Entered 10/17/2007 16:55
Admitted 10/17/2007 18:31
Admitted 10/18/2007 10:13
Entered 10/18/2007 10:13
Admitted 10/18/2007 14:11
Entered 10/18/2007 14:11
Admitted 10/18/2007 17:39
Entered 10/18/2007 17:39
Admitted 10/18/2007 17:43
Entered 10/18/2007 17:43
Admitted 10/19/2007 10:46
Entered 10/19/2007 10:46
Admitted 10/19/2007 16:39
Entered 10/19/2007 16:39
Admitted 10/19/2007 18:40
Entered 10/19/2007 18:40
Admitted 10/19/2007 21:13
Admitted 10/22/2007 10:33
Entered 10/22/2007 10:33
Admitted 10/22/2007 13:17
Entered 10/22/2007 13:17
Admitted 10/22/2007 14:03
Entered 10/22/2007 14:03
Admitted 10/22/2007 15:58
Entered 10/22/2007 15:58
Admitted 10/22/2007 18:51

Then, in cell $D$2, enter the formula:

=IF(AND(B2=B1,B2=B3),"Delete","")

....and fill down. Then use AutoFilter to show only rows that are blank in
column $D.