Re: [ExcelVBA] Re: Excel VBA code for deletng rows that contain hours within special range?

 

Hello,
 
This worked for me. Greater than equal to 10 hrs. in column with Difference.
 
data:
Date Entrance Exit Difference
7/12/2011 11:34:36 12:01:12 0:26:36
7/12/2011 11:26:59 11:57:15 0:30:16
7/12/2011 7:24:12 17:58:00 10:33:48
7/12/2011 7:37:10 19:56:47 12:19:37
7/12/2011 7:45:14 18:00:42 10:15:28
7/12/2011 7:49:01 19:00:32 11:11:31
7/12/2011 7:59:29 18:20:40 10:21:11
7/12/2011 7:32:24 17:57:04 10:24:40
7/12/2011 18:15:56
7/12/2011 7:44:40 18:00:18 10:15:38
7/12/2011 8:09:47
7/12/2011 7:08:43 18:56:08 11:47:25
7/12/2011 8:09:45
7/12/2011 8:00:43 19:19:01 11:18:18
7/12/2011 7:46:39 18:05:07 10:18:28
7/12/2011 18:15:59
 

 
From: Farid <adler666608@yahoo.com>
To: ExcelVBA@yahoogroups.com
Sent: Sunday, July 17, 2011 8:49 PM
Subject: [ExcelVBA] Re: Excel VBA code for deletng rows that contain hours within special range?

 

Hi, thanks for the code but it deleted all the rows containing data. Could you edit it to suit my needs, please? Thanks in advance..

--- In ExcelVBA@yahoogroups.com, David Lanman <who1012000@...> wrote:
>
> Hi,
>  
> Try this:
> Sub Macro1()
>   Range("D1").Select
>   Do Until ActiveCell.Offset(0, -3).Value = ""
>     ActiveCell.Offset(1, 0).Select
>     If ActiveCell.Value >= 0.416666666666 Then
>       ActiveCell.Rows("1:1").EntireRow.Select
>       Selection.Delete Shift:=xlUp
>       ActiveCell.Offset(-1, 3).Range("A1").Select
>     End If
>   Loop
>    
> End Sub
>
> Thanks,
> David L
>
> From: Farid <adler666608@...>
> To: ExcelVBA@yahoogroups.com
> Sent: Friday, July 15, 2011 9:13 PM
> Subject: [ExcelVBA] Re: Excel VBA code for deletng rows that contain hours within special range?
>
>
>  
>
> ¹ Date Entrance Exit Difference
> 1 7/12/2011 11:34:36 12:01:12 0:26:36
> 2 7/12/2011 11:26:59 11:57:15 0:30:16
> 3 7/12/2011 7:24:12 17:58:00 10:33:48
> 4 7/12/2011 7:37:10 19:56:47 12:19:37
> 5 7/12/2011 7:45:14 18:00:42 10:15:28
> 6 7/12/2011 7:49:01 19:00:32 11:11:31
> 7 7/12/2011 7:59:29 18:20:40 10:21:11
> 8 7/12/2011 7:32:24 17:57:04 10:24:40
> 9 7/12/2011 18:15:56
> 10 7/12/2011 7:44:40 18:00:18 10:15:38
> 11 7/12/2011 8:09:47
> 12 7/12/2011 7:08:43 18:56:08 11:47:25
> 13 7/12/2011 8:09:45
> 14 7/12/2011 8:00:43 19:19:01 11:18:18
> 15 7/12/2011 7:46:39 18:05:07 10:18:28
> 16 7/12/2011 18:15:59
>
> Thank you for advice, but that's not helpful enough and besides time-wasting. That is the format I'm talking about. Employees should come before 8:00:00 and leave at 18:00:00. Some of them may leave before 18:00:00 unless they worked 10:00:00. When I use firltering, there so many things to unselect that it takes a lot of time. Hopefully, you can help me to get away with this. Thanks
>
> --- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@> wrote:
> >
> > Use auto filter and then delete the rows.
> >
> > Regards, Dave S
> >
> > ----- Original Message -----
> > From: "Farid" <adler666608@>
> > To: <ExcelVBA@yahoogroups.com>
> > Sent: Friday, July 15, 2011 9:56 PM
> > Subject: [ExcelVBA] Excel VBA code for deletng rows that contain hours
> > within special range?
> >
> >
> > > Hello, I have an excel list of workers and the hours they spent at work. I
> > > need to delete rows of workers who spent at least 10:00:00 hours. The
> > > format of time is "hours:minutes:seconds". Thanks
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > ----------------------------------
> > > Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> > > here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> > > the ARCHIVES for EXCEL VBA.
> > >
> > > ----------------------------------
> > > Visit our ExcelVBA group home page for more info and support files:
> > > http://groups.yahoo.com/group/ExcelVBA
> > >
> > > ----------------------------------
> > > More free tutorials and resources available at:
> > > http://www.mousetrax.com
> > >
> > > ----------------------------------Yahoo! Groups Links
> > >
> > >
> > >
> > >
> > >
> > > -----
> > > No virus found in this message.
> > > Checked by AVG - www.avg.com
> > > Version: 10.0.1390 / Virus Database: 1516/3763 - Release Date: 07/13/11
> > >
> >
>
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (7)

Recent Activity:

• New Members 8

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___