Test if Column B is autofiltered

[CLR]

Hi All.....
I have a macro that I would like to first check if the sheet1 has been
Autofiltered for any selection in Column B before it will continue to
run.......the sheet may or may not be also Autofiltered for other columns,
but it must also be Autofiltered for a selection in column B to qualify.

Any help would be much appreciated.

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Michael]

You can modify this instruction to be run on one or multiple columns; as is,
it will check in the whole sheet.
If Worksheets("Sheet1").AutoFilterMode Then
isOn = "On"
Else
isOn = "Off"
End If
MsgBox "AutoFilterMode is " & isOn

 

[CLR]

Thanks Michael, but I guess I wasn't clear in my post. I only want my macro
to run IF the Autofilter is on for Column B, AND a selection has been made in
Column B. Column B is a list of names, and I only want to run the macro
against ONE SELECTED name.

How can I modify your code to do that?

Vaya Con Dios,
Chuck, CABGx3

 

[Michael]

OK, modify my sub and make sure you also copy the function.
Function courtesy of http://www.ozgrid.com/VBA/autofilter-criteria.htm
Sub IsFilteron()
Dim iLastRow As Double
Dim numCells As Double

iLastRow = Range("B65536").End(xlUp).Row
numCells = Range("B1:B" & iLastRow).SpecialCells(xlCellTypeVisible).Count

If numCells <> iLastRow Then ' This determines if there is a filter on B
strFltrCrit = AutoFilter_Criteria(Range("B1")) 'This returns the
criteria used on the filter
MsgBox (strFltrCrit)
'Call yourmacro
End If

End Sub
Function AutoFilter_Criteria(Header As Range) As String

Dim strCri1 As String, strCri2 As String
Application.Volatile
With Header.Parent.AutoFilter

With .Filters(Header.Column - .Range.Column + 1)

If Not .On Then Exit Function
strCri1 = .Criteria1

If .Operator = xlAnd Then

strCri2 = " AND " & .Criteria2

ElseIf .Operator = xlOr Then

strCri2 = " OR " & .Criteria2

End If

End With

End With
AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2

End Function
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[Peter T]

Another way (anywhere in col-b)

Sub test()

MsgBox ColFilterOn(ActiveSheet, "B")
MsgBox ColFilterOn(ActiveSheet, 2)

End Sub

Function ColFilterOn(ws As Worksheet, col) As Boolean
Dim af As AutoFilter

If VarType(col) = vbString Then
col = ws.Range(col & 1).Column
End If

On Error GoTo errExit
Set af = ws.AutoFilter
On Error GoTo 0
If Not af Is Nothing Then
ColFilterOn = Not Intersect(Columns(col), af.Range) Is Nothing
End If

errExit:

End Function

Regards,
Peter T

 

[CLR]

Almost there Michael...........
This one will give a "pass" if ANY column is filtered on, and will return a
blank window if the column is not "B". Somehow if we could take that return
and allow a "pass" only if the return was NOT blank.

Vaya con Dios,
Chuck, CABGx3

 

[Michael]

How about this:
ub IsFilteron()
Dim iLastRow As Double
Dim numCells As Double

iLastRow = Range("B65536").End(xlUp).Row
numCells = Range("B1:B" & iLastRow).SpecialCells(xlCellTypeVisible).Count

If numCells <> iLastRow Then
strFltrCrit = AutoFilter_Criteria(Range("B1"))

If strFltrCrit = "" Then
MsgBox (strFltrCrit) ' This is where your pass macro goes when blank
is returned
End If
'Call yourmacro
End If

End Sub
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[CLR]

Thanks Peter, but this one returns "true" no matter what column is filtered
on....or actually if none are and just the Autofilter is turned on.

Vaya con Dios
Chuck, CABGx3

 

[Peter T]

Maybe I misudnerstood the objective, as written returns true/false if a
filter exists in the column (not necessarily selected). Is this is what you
want

Sub test()

MsgBox ColFilterOn(ActiveSheet, "B")
MsgBox ColFilterOn(ActiveSheet, 2)

End Sub

Function ColFilterOn(ws As Worksheet, col) As Boolean
Dim af As AutoFilter, f As Filter

If VarType(col) = vbString Then
col = ws.Range(col & 1).Column
End If

On Error GoTo errExit
Set af = ws.AutoFilter
On Error GoTo 0
If Not af Is Nothing Then
If Not Intersect(Columns(col), af.Range) Is Nothing Then
For Each f In af.Filters
If f.Parent.Range.Column = col Then
ColFilterOn = f.On
Exit For
End If
Next
End If
End If

errExit:

End Function

Regards,
Peter T

 

[CLR]

Ok Michael........
A tiny bit of tweaking of your last code and this is what I got that does
exactly what I want......

Sub IsFilteron()
Dim iLastRow As Double
Dim numCells As Double
iLastRow = Range("B65536").End(xlUp).Row
numCells = Range("B1:B" & iLastRow).SpecialCells(xlCellTypeVisible).Count
If numCells <> iLastRow Then
strFltrCrit = AutoFilter_Criteria(Range("B1"))
If strFltrCrit = "" Then
GoTo 200
Else
MsgBox "call your macro"
End If
Else
200
MsgBox "Do a filter on Column B first"
100
End If
End Sub

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String
Application.Volatile
With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)
If Not .On Then Exit Function
strCri1 = .Criteria1
If .Operator = xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr Then
strCri2 = " OR " & .Criteria2
End If
End With
End With
AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2
End Function

This solves my problem.....your work was brilliant Michael. There is
absolutely no way I could have gotten there without your help. I wish I
could do more for you than just give you one little checkmark that you were
"helpful". Not only do I appreciate your solution, but also for your
sticking with me until my problem was solved. You are definately "Grade A"
in my book, both as a Programmer, and as a Person.

Many thanks again,
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Hi Peter..........
It was just a case of my poor explaining.........if you check my last post
to Michael, you will see what I was trying to do. A slight tweak of his
final offering solved my problem. Thanks anyway for your interest and
submission.

Vaya con Dios,
Chuck, CABGx3