If a time stamp is not between a timestamp range delete

[SWAY14]

Hello,
I am still relatively new to VBA and need help. Thank you in advanced.

I am looking for help with creating a VBA if a timestamp in column A is not between 2 timestamps then delete that row. For example if 8/11/2022 4:10:42 PM is not between 5:00:00 and 5:59:00 then delete the entire row. Would something like this be possible? I currently have a VBA which deletes the row if not not todays date but I would like to break it down further by if today date and not between essentially 5-6 pm then delete row. thank you very much in advanced.

 

[6StringJazzer]

Your question is out of context but here is the answer to what you specifically asked. BTW your example uses AM times but you said you wanted PM so I reflect PM in the code.

Dim R As Long ' Row
Dim CellTime As Double

' R is set somewhere in here

CellTime = Cells(R, "A") - Int(Cells(R, "A"))
If CellTime < CDate("17:00:00") Or CellTime  > CDate("17:59:00") Then
   Rows(R).EntireRow.Delete
End If

 

[SWAY14]

Your question is out of context but here is the answer to what you specifically asked. BTW your example uses AM times but you said you wanted PM so I reflect PM in the code.

Dim R As Long ' Row
Dim CellTime As Double

' R is set somewhere in here

CellTime = Cells(R, "A") - Int(Cells(R, "A"))
If CellTime < CDate("17:00:00") Or CellTime  > CDate("17:59:00") Then
   Rows(R).EntireRow.Delete
End If

Thank you very much for your help and pointing out my error with the time I do see that the timestamps work in 24hr now. Unfortunately when I run your code I get

 

[SWAY14]

Thank you very much for all your help! I was wondering if my exciting code could also work?

Option Explicit
Sub testtime()

Application.ScreenUpdating = False

Dim i As Long, lr As Long, ws2 As Worksheet
Set ws2 = Sheets("Sheet2")
lr = ws2.Range("A" & Rows.Count).End(xlUp).Row

For i = lr To 2 Step -1
If ws2.Cells(i, 1).Value < ("17:00:00") Then
ws2.Cells(i, 1).EntireRow.Delete
End If
Next i

Application.ScreenUpdating = True

End Sub

this is what my table stamp time is

the issue I am getting is that if time is less than 17:00:00 it delete but if time is greater than 17:00:00 it also deletes. Any suggestions of what I am doing wrong ? Not sure if this acceptable for me to post this here or if I should upload a new question thank you!

 

[6StringJazzer]

Thank you very much for your help and pointing out my error with the time I do see that the timestamps work in 24hr now. Unfortunately when I run your code I get

You get what?

 

[6StringJazzer]

this is what my table stamp time is

the issue I am getting is that if time is less than 17:00:00 it delete but if time is greater than 17:00:00 it also deletes.

This code has two errors.

First, your cell has a date plus a time. You just need to check the time only, so you must strip off the date. This is why I provided this line in my code

CellTime = Cells(R, "A") - Int(Cells(R, "A"))

Second, you are comparing a date/time to a string ("17:00:00"). That won't work. You have to compare it to a time. That is why I included an explicit conversion to time in my code CDate("17:00:00").

Next time you post code I suggest highlighting the code then clicking the VBA button to preserve the formatting of the code.

Please try this.

Sub testtime()

   Application.ScreenUpdating = False
   
   Dim i As Long, lr As Long, ws2 As Worksheet
   Set ws2 = Sheets("Sheet2")
   lr = ws2.Range("A" & Rows.Count).End(xlUp).Row
   
   For i = lr To 2 Step -1
      If (ws2.Cells(i, 1).Value - Int(ws2.Cells(i, 1).Value)) < CDate("17:00:00") Then
         ws2.Cells(i, 1).EntireRow.Delete
      End If
   Next i
   
   Application.ScreenUpdating = True

End Sub

 

[SWAY14]

You get what?

Ok I get it now. I had a feeling this was the case. Although I need the date since this is the main way of deleting the rows I dont want and then I want to delete further by removing by time. I can see why your striping the date. Also in the code you uploaded you have "' R is set somewhere in here" in the code. I did get a range error when running it would this be the reason why? again im still semi new to VBS so my apologies for the rookie mistakes. Thank you!

 

[SWAY14]

This code has two errors.

First, your cell has a date plus a time. You just need to check the time only, so you must strip off the date. This is why I provided this line in my code

CellTime = Cells(R, "A") - Int(Cells(R, "A"))

Second, you are comparing a date/time to a string ("17:00:00"). That won't work. You have to compare it to a time. That is why I included an explicit conversion to time in my code CDate("17:00:00").

Next time you post code I suggest highlighting the code then clicking the VBA button to preserve the formatting of the code.

Please try this.

Sub testtime()

   Application.ScreenUpdating = False
  
   Dim i As Long, lr As Long, ws2 As Worksheet
   Set ws2 = Sheets("Sheet2")
   lr = ws2.Range("A" & Rows.Count).End(xlUp).Row
  
   For i = lr To 2 Step -1
      If (ws2.Cells(i, 1).Value - Int(ws2.Cells(i, 1).Value)) < CDate("17:00:00") Then
         ws2.Cells(i, 1).EntireRow.Delete
      End If
   Next i
  
   Application.ScreenUpdating = True

End Sub

I just saw the addition that you made to my existing code and it works. Thank you very much for all your information and for the addition to my code. This has really helped a lot!

 

[6StringJazzer]

If my answer helped you please mark my post as the solution.