Delete table rows where a cell in D contains certain numbers

[victormiyagi]

Hi all,

I'm stuck somewhere very silly (I'm sure), but I can't seem to be able to get my macro to work. I have a spreadsheet with all of our call centre's incoming calls. I need my macro to delete all the rows with calls received outside business hours (I have the hours in column D). This is what I was trying to use but it's not working:

Sub removecallsoutsidebh()

 ' Remove calls outside business hours
   
        Range("D2").Select
    Application.ScreenUpdating = False
    With ActiveSheet.Range("D1", Range("D" & Rows.Count).End(xlUp))
        .AutoFilter Field:=1, Criteria1:=Array("1", "2", "3", "4", "5", "6", "7", "17", "18", "19", "20", "21", "22", "23"), Operator:=xlFilterValues
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True

End Sub

Interestingly, the above worked when I wanted my macro to delete all the rows with calls made to our two fax numbers, as I don't need to monitor those. I used the same macro but with two values and it works perfectly. It deletes all the rows with calls made to our fax numbers. If it helps, that first macro runs before my data is converted into a table....not sure that changes anything.

Any help will be greatly appreciated!

Thank you!

[gmr4evr1]

I just tried it on a mock-up version and it worked as expected. How is column D formatted?

[victormiyagi]

Thanks for trying a mock-up! It was converted to General earlier in the macro:

With Selection
.NumberFormat = "General"
.Value = .Value
End With

Is your data within a table?

[victormiyagi]

I think I may know what's wrong....the macros I'm posting here are for data outside of a table. I created them before my data was transformed into a table and they were working.

Does anyone know how to accomplish what I need if the values are within a table?

[gmr4evr1]

No, my data was not in a table. If I tried it in a table, it did not work correctly.

[victormiyagi]

That's what I thought. I just added a code to convert my table back to a range and then it worked. There has to be a way to do it within a table but I can't find it anywhere, so converting it back to a range seemed like the easiest fix for now.

Thanks anyway!