-
May 23rd, 2016, 01:10 PM
#1
Thread Starter
Member
Deleting row based on time
Hello
I'm using Excel 2010 and am trying to delete rows that are outside of the range 6am to 6pm.
So far I have ...
Code:
Sub DeleteTime()
Dim ws As Worksheet
Dim Lastrow As Long
Dim j As Long
Set ws = ActiveSheet
With ws
Lastrow = .Range("c" & Rows.Count).End(xlUp).Row 'assumes every row in column C has a value
For j = Lastrow To 2 Step -1
If Range("c" & j) < "06:00:00" Or Range("c" & j) > "18:00:00" Then
.Range("c" & j).EntireRow.Delete
End If
Next j
End With
End Sub
but it deletes about everything except entries starting with zero.
I assume it has trouble with my format of "06:00:00"
thanks for any ideas or suggestions
-
May 23rd, 2016, 02:49 PM
#2
Thread Starter
Member
Re: Deleting row based on time
I tried modifying this a bit to simplify
Code:
Sub DeleteTime()
Dim ws As Worksheet
Dim Lastrow As Long
Dim j As Long
Set ws = ActiveSheet
With ws
Lastrow = .Range("c" & Rows.Count).End(xlUp).Row
For j = Lastrow To 2 Step -1
If Range("c" & j) > "18:00:00" Then .Range("c" & j).EntireRow.Delete
Next j
End With
End Sub
but it still doesn't seem to delete much
-
May 23rd, 2016, 04:30 PM
#3
Re: Deleting row based on time
how is the time stored in excel column? text or timevalue?
you can try
Code:
If Range("c" & j) > cdbl(timevalue("18:00:00")) Then
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 29th, 2016, 01:07 PM
#4
Thread Starter
Member
Re: Deleting row based on time
It is stored as 18:45:40 sort of military time. Just as an aside I notice this time will show in the formula bar as 6:45:40.
Can I use this format or do I need to convert it with something like LTime = TimeValue("18:30:12")? then see if LTime is
outside my range?
-
May 29th, 2016, 04:06 PM
#5
Re: Deleting row based on time
the suggestion i posted above was tested to work correctly, on my machine /workbook, test it on yours
regardless of how a time displays, excel stores date /time values as doubles, unless it is forced to be a string, which can happen, then both should be coerced into numerical values
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 31st, 2016, 04:56 PM
#6
Thread Starter
Member
Re: Deleting row based on time
Thanks for your reply
Well I must be daft because I'm not seeing how this line is going to delete a range and it
didn't on mine so I tried ...
Code:
Sub DeleteTime()
Dim ws As Worksheet
Dim Lastrow As Long
Dim j As Long
Dim tv As Double
Set ws = ActiveSheet
With ws
Lastrow = .Range("c" & Rows.Count).End(xlUp).Row
For j = Lastrow To 2 Step -1
If Range("c" & j) < CDbl(TimeValue("6:00:00")) Or Range("c" & j) > CDbl(TimeValue("18:00:00")) Then
' If Range("c" & j) < "06:00:00" Or Range("c" & j) > "18:00:00" Then
.Range("c" & j).EntireRow.Delete
End If
Next j
End With
End Sub
but it doesn't delete anything
-
Jun 1st, 2016, 05:10 AM
#7
Re: Deleting row based on time
Well I must be daft because I'm not seeing how this line is going to delete a range
well of course i assumed you would still keep the remainder of the code, to do the actual deleting
i tested your code as posted, works perfectly, all rows with times outside of 6AM to 6PM were deleted
if it is still not working post a sample workbook that demonstrates the problem
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|