Compile Error: Sub or Function not defined

[johnmerlino]

Hey all,

I have a macro that filters out data using auto filter. Problem is once everything is complete, I only want to keep the visible cells and destroy everything else. So I added a For Each In loopm to iterate through active cells and set their auto filter mode to false, which hopefully removes any filtering. Unfortunately it gives me a compire error highlighting the text "specialCell":

Sub sortify()
'
' sortify Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Application.ScreenUpdating = False
    
    Range("A1:E519").Select
    Selection.Cut Destination:=Range("A2:E520")
    Range("A2:E520").Select
    
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "A "
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "B"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "C"
    Range("D1").Select
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "D"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "E"
    Range("A1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$E$520"), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight8"
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
        "=Real Prop*", Operator:=xlAnd
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
        "=DF*", Operator:=xlAnd
        
    ActiveSheet.ListObjects("Table1").Sort.SortFields.Clear
    ActiveSheet.ListObjects("Table1").Sort.SortFields.Add _
        Key:=Range("Table1[[#All],[E]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("C:E").Select
    Selection.Cut
    Selection.SpecialCells(xlCellTypeVisible).Select
    Range("F1").Select
    ActiveSheet.Paste
    
    Columns("A:E").Select
    Selection.EntireColumn.Hidden = True
    
    Rows("1:1").Select
    Range("F1").Activate
    Selection.EntireRow.Hidden = True
    
    For Each cell In SpecialCells(xlCellTypeVisible)
    cell.AutoFilterMode = False
    End With
    
    
End Sub

 

[schielrn]

I think you are missing the range or cell part of this statement?

For Each cell In cells.SpecialCells(xlCellTypeVisible)

Maybe that helps?

 

[johnmerlino]

It then gives me error "end with without with"

Also, do you think this for each loop will remove all the auto filtering from visible cells? I'm hoping it will.

 

[Blade Hunter]

It then gives me error "end with without with"

    For Each cell In SpecialCells(xlCellTypeVisible)
    cell.AutoFilterMode = False
    End With

Change to

    For Each cell In SpecialCells(xlCellTypeVisible)
    cell.AutoFilterMode = False
    Next

 

[johnmerlino]

Now it says object doesn't support this property or method. "cell.AutoFilterMode = False"
So basically it's saying AutoFilterMode is not a property of the cell object. Now presumably if this works like any other programming language, cell should refer to the current index of visible cells, so am I missing something here?

Thanks for response.

 

[Blade Hunter]

To be honest, I don't know as I have never use specialcells but doesn't autofilter (or the removal of) work on a range as opposed to a cell?