checking to see if cell time value is in range

[Prevost]

Hi There. I have a large column of cells that contain a time value (just the time in HH:MM) and I would like to delete all the cell rows that are not in the time range from 8:00AM to 5:00PM. I am struggling with checking time values through VBA and checking to see if the time falls in a certain range.

I wanted to use something like:

If 5:00PM < cell < 11:59PM OR 12:00AM < cell < 8:00AM then
(delete cell row)
end if

I don't know how to check for the time range of the cell.

Any help or suggestions are appreciated. Thanks!

 

[ParamRay]

.
.


Assuming your time values are in column A, then you could try this:

Sub DeleteRows()

  Dim rngUsedCells   As Range
  Dim rngUsedCell    As Range
  Dim dblTimeDec     As Double
  Dim alngDelRows()  As Long
  Dim lngDelRowCount As Long
  Dim lngCounter     As Long
  
  Set rngUsedCells = Intersect(ActiveSheet.UsedRange, Columns("A"))
  
  For Each rngUsedCell In rngUsedCells
    
    If IsDate(rngUsedCell.Value) Then
      dblTimeDec = rngUsedCell.Value - Int(rngUsedCell.Value)
      
      If dblTimeDec < (8 / 24) Or dblTimeDec >= (17 / 24) Then
        lngDelRowCount = 1 + lngDelRowCount
        ReDim Preserve alngDelRows(1 To lngDelRowCount)
        alngDelRows(lngDelRowCount) = rngUsedCell.Row
      End If
    
    End If
  
  Next rngUsedCell
  
  For lngCounter = lngDelRowCount To 1 Step -1
    Rows(alngDelRows(lngCounter)).Delete
  Next lngCounter

End Sub