#REF! error with VBA auto-filter formula to delete rows that contain text

[shagan101]

Hi there,

I'm struggling with a #REF! error showing up in certain cells after running a script to remove cells containing the specific text below. Column B is a =text(A#,"ddd") formula but column A has no references to any other cells.... Have to run the same routine every day and would really like a macro that doesn't break the data. Any suggestions? See images below of snippets of sheets.

Sub deleteOlympicsrows()
    Rows("1:1").Select
    Selection.AutoFilter
    Range("D1").Select
    ActiveSheet.Range("$A$1:$C$10000").AutoFilter Field:=3, Criteria1:= _
        "=*OLYMPICS*"
    ActiveSheet.Range("$A$1:$C$10000").Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.delete
    ActiveSheet.AutoFilterMode = False
End  Sub

Before (left) and after (right)

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Could we have that sample data (A1:C34), including formulas, with XL2BB so that we can test with your actual situation?
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.