I wrote this macro.
I'm not happy with it.
I'm sure there's an easier way.
The problem is that there are so many conditions that need to be checked.
You have two records in your sample file that seem to be suspicious.
Both are for ID no 72619984.
The End time is before the Start time.
Rather than GUESS what to do, I decided that since they were complete duplicate
records,
perhaps this was sample data and would not occur normally.
(watch for email line wrapping)
---------------------------------------------------------
Option Explicit
Sub MoveData()
Dim R, nRows, dRow
Dim ShtSource, ShtDest
Dim lStart, lEnd
Dim dStart, dEnd
Dim TimeStart As Double, TimeEnd As Double
ShtSource = "Sheet1"
ShtDest = "Sheet2"
lStart = 0.520833333 '12:30:00 PM
lEnd = 0.552083333 '1:15:00 PM
dStart = 0.833333333 '8:00:00 PM
dEnd = 0.864583333 '8:45:00 PM
nRows =
Application.WorksheetFunction.CountA(Sheets(ShtSource).Range("A1:A65000"))
dRow =
Application.WorksheetFunction.CountA(Sheets(ShtDest).Range("A1:A65000"))
For R = 2 To nRows
TimeStart = Sheets(ShtSource).Cells(R, "E").Value
TimeEnd = Sheets(ShtSource).Cells(R, "F").Value
TimeStart = TimeStart - Int(TimeStart) 'Remove "Date"
TimeEnd = TimeEnd - Int(TimeEnd) 'Remove "Date"
'---------------------------------------------------
' INCLUDE if
'- the start time is before Lunch or after dinner
'- or start time is after lunch and before dinner
'---------------------------------------------------
If ((TimeStart < lStart) Or (TimeStart > dEnd) _
Or ((TimeStart > lEnd) And (TimeStart < dStart))) Then
'---------------------------------------------------
' INCLUDE if
'- The end time is before Lunch or after dinner
'- or end time is after lunch and before dinner
'---------------------------------------------------
If ((TimeEnd < lStart) Or (TimeEnd > dEnd) _
Or ((TimeEnd > lEnd) And (TimeEnd < dStart))) Then
'---------------------------------------------------
' EXCLUDE if
'- the start time is before Lunch begins
'- AND end time is after Lunch ends
'- OR start time is before dinner begins
'- AND end time is after dinner ends
'---------------------------------------------------
If (((TimeStart < lStart) And (TimeEnd > lEnd)) _
Or ((TimeStart < dStart) And (TimeEnd > dEnd))) Then
Else
dRow = dRow + 1
Sheets(ShtDest).Range("A" & dRow & ":G" & dRow) = _
Sheets(ShtSource).Range("A" & R & ":G" & R).Value
End If
End If
End If
Next R
End Sub
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
________________________________
From: rahul chow <rahul8...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Fri, September 7, 2012 7:38:20 AM
Subject: $$Excel-Macros$$ Macro to filter data based on Time
Hi All,
I want a Macro to filter data based on Time.
If Startdate and Enddate containns timings of 12:30PM to 1:15 PM(lunch
time) and
8:00 PM to 8:45 PM(Dinner Time), then i want to exclude those rows from present
sheet, and copy into separate sheet.
Here i am attaching excel file and Hilighted some rows which need to be exclude
form present sheet and import to separate sheet.
Thanks
Rahul
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners
and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
