Macro and AutoFilter

[ed.ayers315]

Hello,

The below code is giving me problems depending on the status of AutoFilter. If the Autofilter is on when the macro is envoked, it does not filter the desired area because it turns it off, if the autofilter is off it filters the intended area.

If I write in

Selection.AutoFilter

and it is not on I get an error.

Is there a way to write in a if

Selection.AutoFilter

is on, leave on, if else go on?

Thanks

Sub COMPILE_VESSEL_DATA()
'
' COMPILE_VESSEL_DATA Macro

'
If MsgBox("Are you sure you want to run this Macro. YOU DID NOT REALLY THINK I WOULD LET YOU MESS UP YOUR DATA BY ACCIDENT!!", vbYesNo + vbQuestion) = vbNo Then Exit Sub
ActiveSheet.Unprotect
Range("B3:J58").Select
Selection.CheckSpelling SpellLang:=1033
Range("N113:N120").Select
ActiveSheet.Range("$N$113:$N$120").AutoFilter Field:=1, Criteria1:="1"
Range("O113").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:An7").Select
Selection.Copy
Sheets("Vessel_Accumulative_History").Select
Range("B1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("3:3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Rows.AutoFit
Range("B1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("VESSELS").Select
Application.CutCopyMode = False
Selection.AutoFilter
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-12
Rows("3:65").Select
ActiveSheet.Unprotect
Selection.Rows.AutoFit
Range("M2:M65").Select
Selection.AutoFilter
ActiveSheet.Range("$m$3:$m$66").AutoFilter Field:=1, Criteria1:="1"
Range("C12:D12").Select
ActiveSheet.Protect
Range("C12:D12").Select
ActiveSheet.Protect
End Sub

 

[VoG]

Maybe

If ActiveSheet.AutoFilterMode = True Then
'do nothing
Else
'turn it on
End If

 

[ed.ayers315]

Thanks VoG, works great!

 

[p45cal]

While AutofilterMode will work nearly all the time, there may be a time it'll bite you on the bum. It actually returns whether or not the dropdown arrows are visible or not. So you could have an autofilter on the sheet with the arrows not showing and AutoFilterMode will be false.
A more robust test for the presence of an autofilter on the sheet is to apply the autofilter method to the sheet to return an autofilter object; if it doesn't return that object there is no autofilter on the sheet.

If ActiveSheet.AutoFilter Is Nothing Then

 

[ed.ayers315]

Thanks for the extra info, have a great night p45cal.