asked on

Code that will check for AutoFilter mode before running macro, and adjust accordingly

I've tried some of the obvious online solutions such as "Activesheet.ShowAllData," but I can't get anything to work. If [BT5] is already in filter mode then the code below works very nicely. But if it isn't, then I need something that will deal with the two other possibilities:

AutoFilter is not on.

AutoFilter is active on another range.

Thanks,
John

Sub Filter4ShortRows()
Application.ScreenUpdating = False
    If [BT3] = "" Then
        [BT5].AutoFilter Field:=1, Criteria1:="<>"
        [BT4].Calculate
        [BT3] = 1
    Else
        [BT5].AutoFilter Field:=1, Criteria1:="="
        [BT3] = ""
    End If
    [BT5].Select
Application.ScreenUpdating = True
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

Thanks, Dave. I needed to make a little adjustment but it works great. I tried ActiveSheet.AutoFilterMode = False earlier but I must have had something else going on that made it not work. Thank you for getting me to try it again.

Have a great weekend!

John

Sub Filter4ShortRows()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
    If [BT3] = "" Then
        [BT5:BT777].AutoFilter Field:=1, Criteria1:="<>"
        [BT4].Calculate
        [BT3] = 1
    Else
        [BT3] = ""
    End If
    [BT5].Select
Application.ScreenUpdating = True
End Sub

Open in new window