Macro to Filter and Delete Row Not Deleting Visible Filtered Rows

[dversloot1]

Hello,

I'm working through a filter macro to delete unecessary rows of data from my dataset.

- I have a Dynamic Range for my dataset called "CanadaData"
- I'm trying to delete rows from the 5th column of my dataset for cells containing "DIRECTSHIP"

The macro filters the range fine, but when if comes to deleting the row, the macro stops. Any ideas why?

Sub CanadaWarehouseFilter()
x = Range("E" & Rows.Count).End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("E22:E" & x), "DIRECTSHIP") > 0 Then

With Range("CanadaData")
.AutoFilter
.AutoFilter Field:=5, Criteria1:="DIRECTSHIP"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRows.Delete
End With
End If
Range("A2:C2").Select
End Sub

 

[pboltonchina]

Try this on a copy of your data

Sub Delete_DIRECTSHIP()
Application.ScreenUpdating = False
Dim LR As Long, i As Long
LR = Range("E" & Rows.count).End(xlUp).Row
For i = LR To 1 Step -1
If Range("E" & i).Value = "DIRECTSHIP" Then Rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub

 

[dversloot1]

Try this on a copy of your data

Awesome!

I changes your LR to 1 to LR to 22 so that my data in column E in rows 1-21 aren't deleted

Thanks a bunch!