VBA: Deleting rows after 2 columns have been filtered.

[TitoBurrito]

Hello,

After research and trial and error I am unable to get this to work.

Here is my code:

With Sheet2.Range("A1:BM1")
.AutoFilter Field:=13, Criteria1:="*EMEA*", Operator:=xlOr, Criteria2:="*Oceania*"
.AutoFilter Field:=42, Criteria1:="="
.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
End With
Sheet2.AutoFilterMode = False

I am trying to filter column 13 with criteria and column 42 for blanks, then delete all the rows that that are populated.
The filtering is working. The deleting of the rows is the problem.
I am getting Run-time error 1004: Application-defined or object-defined error. With the .OFFSET(1,0) ROW BEING HIGHLIGHTED.

Any help would be lovely,
Thank You

 

[kevin9999]

Thought I would throw my 2 cents worth into the mix. Contrary to popular belief, you don't have to select .SpecialCells(xlCellTypeVisible) when deleting rows using the Autofilter method. Consider the following:

Sub Delete_No_Special_Cells()
    With Sheet2.Range("A1").CurrentRegion
        .AutoFilter 13, "*EMEA*", 2, "*Oceania*"
        .AutoFilter 42, "="
        If .Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete '<~~ no special cells selected
        End If
        .AutoFilter
    End With
End Sub

Test it & you should find it works without selecting xlCellTypeVisible

 

[johnnyL]

That line is Offsetting not resizing

I'll just drink a tall glass of ShutTheFUp juice now. You are right again. Dang It.