-4

I am trying to automate some date filtering for work. I want to delete rows if they do not fall under this time stamp

2:00am - 10:00am

So if the time is before 2:00am or after 10:00am delete the entire row. There are multiple rows like this.

I am not sure where to even start because I am very beginner and need an easy code to follow.

Here is what the data would look like

  • You're asking how to complete step 12 when it would seem you want to skip 1-11 – Mr. Tripodi May 17 '19 at 15:31
  • 1
    Welcome to Stack Overflow @Moogurl - Did you try any VBA? Perhaps start with the macro recorder and go from there? If you can share some attempts and report back with specific issues - people will be more able and eager to help. – RugsKid May 17 '19 at 15:34
  • @Mr.Tripodi what do you mean skip 1-11? –  May 17 '19 at 15:51
  • @RugsKid hey I actually tried macro recorder but it doesnt make sense. Because some of the times arent there? let me try again. –  May 17 '19 at 15:51
  • @Moogurl My apologies, for some reasons I was thinking that you wanted to delete rows from a table, such as executing sql command. – Mr. Tripodi May 17 '19 at 15:54
  • @Mr.Tripodi nope. I want to basically scan the F column and delete times according to the scan. But not sure How this is achieved. –  May 17 '19 at 15:57

2 Answers2

0

My VBA is a bit rusty, but this should work

        Sub DelStuff()
        Dim WB As Workbook
        Dim Sheet As Worksheet
        Set WB = ActiveWorkbook
        Set Sheet = WB.ActiveSheet

        For Each r In Sheet.UsedRange.Rows

            If Cells(r.Row, "F").Value <= Date & " " & #2:00:00 AM# Or Cells(r.Row, "F").Value >= Date & " " & #10:00:00 AM# Then
                r.EntireRow.Delete
            End If

        Next r

        End Sub
Mr. Tripodi
  • 809
  • 1
  • 6
  • 7
0

This is similar to this question here where I gave a similar answer. You can give this a try, though this assumes your times are in the 24 hour format and assumes you only want to delete that specific cell and not the entire row.

Sub TimeDelete()
 'Declare your variables
 Dim timeRng As Range, early As Long, late As Long, lrow As Long 

'Finds the last row with a time in it in Column F
 lrow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 6).End(xlUp).Row 

 'Define the times you want to compare against
 early = "02:00:00"
 late = "10:00:00"

 'Specifies the range the times are in
 Set timeRng = ThisWorkbook.Worksheets("Sheet1").Range(Cells(1,6), Cells(lrow,6))

 'Use a For loop to check each value in the range
 For Each Cell In timeRng.Cells
     If TimeValue(Cell.Value) >= TimeValue(early) And TimeValue(Cell.Value) <= TimeValue(late) Then
         Cell.Delete Shift:=xlToLeft 'Deletes cell if it meets the criteria and shifts remaining cells in the row to the left.
     End If
 Next Cell
End Sub
TOTM
  • 107
  • 7