AutoFilter issue - Run-time error '91': Object variable or With block variable not set

[dougmarkham]

Hi Folks,

Happy new year to everyone.

Please would you be willing to help me with a puzzling error message resulting from my AutoFilter VBA code.
Here are the steps I'm asking the VBA to follow:

1) AutoFilter on column 4 to return only N/A.
2) Select first returned cell and then select all cells down to the last
3) Clear contents (so all N/A are cleared)
4) Remove Autofilter on column 4
5) AutoFilter on column 5 to return only N/A.
6) Select first returned cell and then select all cells down to the last
7) Clear contents (so all N/A are cleared)
8) Remove AutoFilter on column 5

Here is the code for the above steps:

Sub FilterHastagNA()' FilterHastagNA Macro
    Worksheets("Sheet1").Activate
    ActiveWorkbook.Worksheets("Sheet1").AutoFilterMode = False
    Range("D2").Select
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=4, Criteria1:= _
        "N/A"
[COLOR=#006400][B]    ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 4).Select (this line doesn't error message)[/B][/COLOR]
    Range(Selection, Selection.End(xlDown)).ClearContents
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=4
    Worksheets("Sheet1").Activate
    ActiveWorkbook.Worksheets("Sheet1").AutoFilterMode = False
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5, Criteria1:= _
        "N/A"
[COLOR=#ff0000][B]    ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 5).Select '(error message occurs when this line tries to run)[/B][/COLOR]
    Range(Selection, Selection.End(xlDown)).ClearContents
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5
End Sub

The problem arises when (as occurs occasionally with this report) there are no N/A in either Column 4 or Column 5: steps 1 to 5 function fine, but I get the following error message on step 6

Run-time error '91': Object variable or With block variable not set

The odd thing is that the VBA code filters on N/A in column 4---then this removes all lines in the table when there is no N/A in column 4---and the next line which tells excel to select the first cell down, this doesn't error message despite there being no cells to select because all lines in the table were filtered out. However, when filtering on Column 5, if there are no N/A in column 5, then the same line of code returns the error message.

Is there a reason why it's error messaging the second time and not the first?
Would you be willing to suggest a modification that solves this issue?

Kind regards,

Doug.

 

[RoryA]

Why are you using activesheet.autofilter instead of ActiveSheet.ListObjects("Table1").autofilter, given that you are trying to manipulate the table?

 

[dougmarkham]

Why are you using activesheet.autofilter instead of ActiveSheet.ListObjects("Table1").autofilter, given that you are trying to manipulate the table?

Hi RoryA,

Thanks for the response, I've been laid out with the common flu virus.
You hit the nail on the head: I guess I shouldn't be trying to use VBA with a head cold. Feeling a tad stupid lol.

Kind regards,

Doug.