PDA

View Full Version : Delete table rows where a cell in D contains certain numbers



victormiyagi
12-04-2017, 11:19 AM
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!

victormiyagi
12-04-2017, 11:25 AM
I think I may know what's wrong....the macro I posted is for data outside of a table. I created it before my data was transformed into a table and it was working.

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