Search code examples
excelvbaautofilter

VBA "Object Required" Error (424) when trying to manipulate/delete filtered rows in vba macro

I am writing a macro with the aim of importing data and generate a cleaned up table.

After importing, I form a table and try to filter and remove irrelevant rows. and naturally filter back to the only one item I want - "Logged" here.

The filter works up to the following line:

ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=10, Operator:=xlFilterValues, _
        Criteria1:=Array("Cancelled", "Done", "On Hold", "PreReg")

Which properly filters what I want. I can view it when I run it in step-wise format and it works. When I try to delete is where things get interesting and not working.

I tried to use .SpecialCells(xlCellTypeVisible).Delete, .SpecialCells(xlCellTypeVisible).EntireRow.Delete, and Select and then with selection doing the same functions. Almost all of them give back Run-time Error 424: Object Required.

I have looked up solutions below: 1- From StackOverFlow multiple posts were using the same solution - specialcells - and as you see it did not work. 2- From the same link as 1, a response offered the following:

ActiveSheet.AutoFilter.Range.Offset(1,0).Rows.SpecialCells(xlCellTypeVisible).Delete(xlShiftUp)

While this solution works, I am trying to use the table as an object and then filter. As This documentation indicates, the output for either cases should be a variant and it seems that applying even .rows in the following manner gives the same error 424:

ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=10, Operator:=xlFilterValues, _
        Criteria1:=Array("Cancelled", "Done", "On Hold", "PreReg").Rows

3- I got suspicious of whether I use names for objects wrong. So I went to the basics and recorded a macro that made the following lines for me:

    Table4.Range.AutoFilter Field:=10, _
     Criteria1:=Array("Cancelled", "Done", "On Hold", "PreReg"), Operator _
     :=xlFilterValues
    Range("Table4").Select
    Selection.EntireRow.Delete ' **** This line does not seem right!!!
    Table4.Range.AutoFilter Field:=10

The challenge this time is repeatability and hidden rows. After running it multiple rounds, I got the two issues: Issue 1: One or more rows get stuck and hidden. As such, in a very unusual way, even if I hold the code Stop and F8 through, it does not allow me to filter back or even show "Logged" - the one I want to keep! Issue 2: Some times it completely deletes the data leaving the table empty! I pasted the data in row 2 and below to keep 1 row blank up top and this issue essentially leaves me with only headers in row 2, hides row 3, and the rest of the excel sheet is blank.

Would you be able to help and point out what am I missing?

Thanks :)

Solution
    • If SpecialCells(xlCellTypeVisible) returns a non-continuous range, EntireRow.Delete raises runtime error 1004.
    • The return value of SpecialCells(xlCellTypeVisible) maybe is empty, adding If before delete rows is necessary.
    • Removing the desired rows after ShowAllData is a good option.
    With ActiveSheet.ListObjects("Table4")
    .Range.AutoFilter Field:=10, Operator:=xlFilterValues, _
        Criteria1:=Array("Cancelled", "Done", "On Hold", "PreReg")
    Dim visRng As Range
    Set visRng = .DataBodyRange.SpecialCells(xlCellTypeVisible)
    If Not visRng Is Nothing Then
        .AutoFilter.ShowAllData
        visRng.Delete
    End If
End With