Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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

0
gabrielPennyback
Asked:
gabrielPennyback
  • 2
2 Solutions
 
dlmilleCommented:
USE:

ActiveSheet.AutoFilterMode = False 'turn off filters

Then your code can set the filters for your sheet.  If you only need the filter on BT5, your code that you've written will set that filter up.

Cheers,

Dave
0
 
dlmilleCommented:
To further elaborate, the attached modification of your code will clear any filters on the active worksheet, then when you set the filter on BT5, it will create a filter on the contiguous range (region) around BT3 - so any fields adjacent to BT3 and down from BT3 will be set with the new data filter.  Then, your criteria on BT3 or BT5 will be set with your subsequent commands.

 
Sub Filter4ShortRows()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = 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


You can be more explict and specify the entire range for the autofilter associated with your BT3 desired criteria setting step....

E.g., Before line 5 you could also add:

    [B5:BT99].AutoFilter

Cheers,

Dave
0
 
gabrielPennybackAuthor Commented:
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

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now