Auto filtering erroring out

[jordanburch]

Sub reconciliation()

'

' reconciliation Macro

'



'

Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select

If ActiveSheet.AutoFilterMode Then

If ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData

End If

Else

If ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData

End If

End If



Range("AR12").Select



ActiveSheet.Range("$A$1:$Ax$6000").AutoFilter Field:=34, Criteria1:= _

"5 - ALC "

ActiveSheet.Range("$A$1:$Ax$6000").AutoFilter Field:=36, Criteria1:="="





Range("as2:as6000").SpecialCells(xlCellTypeVisible).Formula = "=+CONCATENATE("""",RC[-44],RC[-40])"



Range("at2:at6000").SpecialCells(xlCellTypeVisible).Formula = "=+TRIM(RC[-31])"



Range("au2:au6000").SpecialCells(xlCellTypeVisible).Formula = "=+CONCATENATE(RC[-2],RIGHT(RC[-1],4))"



Range("av2:av6000").SpecialCells(xlCellTypeVisible).Formula = "=+SUMIF(C[-1],RC[-1],C[-38])"



Range("aw2:aw6000").SpecialCells(xlCellTypeVisible).Formula = "=+CONCATENATE(RC[-2],RC[-1])"



Sheets("").Select



Range("BD2").Select

ActiveCell.FormulaR1C1 = "=+CONCATENATE(RC[-50],RC[-47],RC[-45])"

Range("BD2").Select

Selection.AutoFill Destination:=Range("BD2:BD488"), Type:=xlFillDefault

Range("BD2:BD488").Select



Range("BE2").Select

ActiveCell.FormulaR1C1 = "=+SUMIF(C[-1],RC[-1],C[-42])"

Range("BE2").Select

Selection.AutoFill Destination:=Range("BE2:BE488")

Range("BE2:BE488").Select

Range("BF2").Select

ActiveCell.FormulaR1C1 = "=+CONCATENATE(RC[-2],RC[-1])"

Range("BF2").Select

Selection.AutoFill Destination:=Range("BF2:BF488")

Range("BF2:BF488").Select

Range("BG2").Select

ActiveCell.FormulaR1C1 = "=+MATCH(RC[-1],'FEB FY21 IDARRS'!C[-10],0)"

Range("BG2").Select

Selection.AutoFill Destination:=Range("BG2:BG488")

Range("BG2:BG488").Select

Columns("BF:BF").EntireColumn.AutoFit

Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select



Columns("AW:AW").ColumnWidth = 8.22

Columns("AW:AW").EntireColumn.AutoFit

Sheets("").Select

Range("BF3").Select

Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select





Range("ax2:ax6000").SpecialCells(xlCellTypeVisible).Formula = "=+MATCH(RC[-1],!C[8],0)"





Sheets("").Select



Range("BD2").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=+CONCATENATE("""",RC[-50],RC[-47],RC[-45])"

Range("BD2").Select

Selection.AutoFill Destination:=Range("BD2:BD488")

Range("BD2:BD488").Select

Range("BH2").Select

Selection.ClearContents

Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select



With ActiveSheet

If ActiveSheet.AutoFilterMode Then

If ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData

End If

Else

If ActiveSheet.FilterMode Then

ActiveSheet.ShowAllData

End If

End If

End With



ActiveSheet.Range("$A$1:$AX$6000").AutoFilter Field:=50, Criteria1:="<>#N/A", _

Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues

Sheets(ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS").Select





Range("aj2:aj6000").SpecialCells(xlCellTypeVisible).Formula = "COMPLETE"







Range("al2:al6000").SpecialCells(xlCellTypeVisible).Formula = ""





End Sub

here is the part it is erroring out on . seems like the range isnt activated or working right. Im not sure whats wrong though. ANy help is appreciated! 1004 autofilter method range of class failed is the error.

ActiveSheet.Range("$A$1:$AX$6000").AutoFilter Field:=50, Criteria1:="<>#N/A", _

Operator:=xlAnd, Criteria2:="<>", Operator:=xlFilterValues

 

[jordanburch]

so the problem seems that there arent any #n/a in the column, how do i account for that in case there are or arent any #n/a values ?