Range to be divided with dates

CESCOBAR

New Member
Joined
Jun 8, 2015
Messages
4
Hey everyone,

I have a range from D14 to j200 (will vary each month) with information regarding employees' productive time. This information has dates and check-in information at each door.

I need to delete the first check in of the day at the entrance and the last check in of the day at the exit for each productive day in april. Entrance info (date, name of the door, time of check-in at that door, time outside of production) is found from columns G to J. Exit info (date, access point, and time) is from columns D to F.

I've been filtering by day and using offset command to move to and delete the first entrance of the day and then moving with .End(x1to right, left or down) to delete the last exit of the day.

Heres the sample code I have. Can you help me optimize it?

Code:
Range("$D$14:$J$14").Select
    Selection.AutoFilter
    ActiveSheet.Range("$D$14:$J$800").AutoFilter Field:=4, Criteria1:=Array("=" _
        ), Operator:=xlFilterValues, Criteria2:=Array(2, "4/1/2015")
    Range("$G$14:$J$14").Select
    Do
    Selection.Offset(1, 0).Select
    Loop While Selection.EntireRow.Hidden = True
    Selection.ClearContents
    Range("$D$14:$F$14").Select
    Selection.End(xlDown).Select
    ActiveCell.Resize(1, 3).Select
    Selection.ClearContents
    Range("$G$14:$J$14").Select
    Do
    Selection.Offset(2, 0).Select
    Loop While Selection.EntireRow.Hidden = True
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("$G$14").Select
    Do
    Selection.Offset(1, 0).Select
    Loop While Selection.EntireRow.Hidden = True
    ActiveSheet.Paste
    Range("$D$14:$F$14").Select
    Do
    Selection.Offset(1, 0).Select
    Loop While Selection.EntireRow.Hidden = True
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("$D$14").Select
    Do
    Selection.Offset(1, 0).Select
    Loop While Selection.EntireRow.Hidden = True
    ActiveSheet.Paste
    ActiveSheet.ShowAllData
    Range("$D$14:$J$14").Select

I've only managed to do the proper deletion of data for the first day only... when filtering for the next work day I get stuck
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
619
Office Version
  1. 365
Platform
  1. Windows
Could you do helper columns, a first check in and last check in rank? Then use autofilter based on rank columns for the two helper columns to clear cells then resort range.
 

CESCOBAR

New Member
Joined
Jun 8, 2015
Messages
4
Could you do helper columns, a first check in and last check in rank? Then use autofilter based on rank columns for the two helper columns to clear cells then resort range.

Sorry, C Moore. I think I got lost right there at helper columns... and basically don't know how to do the rest... :confused::confused:
 

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
619
Office Version
  1. 365
Platform
  1. Windows
A helper column is one you add to a data set with a formula to 'help' find something. In this case, column K would be a formula to determine the rank per day of the check-in, and column L rank per day of 'check-out'.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,553
Messages
5,838,075
Members
430,527
Latest member
MyFace2

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
Top