I have an vb script that I run that formats several simular worksheets. One
of the things that is does is turns on the AutoFilter. I would like to have
it un-select one of the values on one of the columns during this format
process.

Example Data
a b
Row1 Ind Value
Row2 C 100
Row3 B 222
Row4 space 50
Row5 D 25
Row6 E 500

Both columns have the Filter On and I want to Un-Select all the values = "C"
in Column A

I know through recording a macro and looking at the code you can set a value
to a selection, but that only works if your selection is always in the same
position in the list. I do not have this option because some of my sheets
may have 2 options while others may have 20. To complicate things further
Value "C" may not even be in the list at all.

Thanks

I am not really sure if the following example is what you need. If not, feel
free to get back to me.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Also note that the Criteria test is case sensitive and hense the use of
Ucase. (It is not essential to assign the criteria to a variable. It can be
used directly in the If test.)

The code only works with simple single criteria setings. If using complex
criteria with And/Or operators then need more code. If multiple criteria is
set in xl2007 then it is quite complex.

Sub FilterExample_3()
Dim ws As Worksheet
Dim strFilter As String
Dim i As Long

Set ws = Worksheets("Sheet1")

With ws
'Test that AutoFilter is turned on
'(If turned off remaining code errors)
If .AutoFilterMode Then
With .AutoFilter
For i = 1 To .Filters.Count
'Test if filter is on
If .Filters(i).On Then
'Assign filter value to variable
strFilter = .Filters(i).Criteria1
If UCase(strFilter) = "=C" Then
'Turn off filter i
.Range.AutoFilter Field:=i
End If
End If
Next i
End With
Else
MsgBox "Autofilter on sheet " & _
ws.Name & " is turned off." & vbLf _
& "Processing terminated."
Exit Sub
End If
End With

End Sub



--
Regards,

OssieMac


"stickandrock" wrote:

I have an vb script that I run that formats several simular worksheets. One
of the things that is does is turns on the AutoFilter. I would like to have
it un-select one of the values on one of the columns during this format
process.

Example Data
a b
Row1 Ind Value
Row2 C 100
Row3 B 222
Row4 space 50
Row5 D 25
Row6 E 500

Both columns have the Filter On and I want to Un-Select all the values = "C"
in Column A

I know through recording a macro and looking at the code you can set a value
to a selection, but that only works if your selection is always in the same
position in the list. I do not have this option because some of my sheets
may have 2 options while others may have 20. To complicate things further
Value "C" may not even be in the list at all.

Thanks

I like it.... It is real close.

My question is though: If I have 20 Columns on my sheet and auto filter is
on All of them but Column G is the filter I want to adjust. How do I select
just that filter for that particular column. in the logic you gave I don't
see the selection for a particular column filter. If I'm missing something
please let me know.

Much Appreciated!

"OssieMac" wrote:

I am not really sure if the following example is what you need. If not, feel
free to get back to me.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Also note that the Criteria test is case sensitive and hense the use of
Ucase. (It is not essential to assign the criteria to a variable. It can be
used directly in the If test.)

The code only works with simple single criteria setings. If using complex
criteria with And/Or operators then need more code. If multiple criteria is
set in xl2007 then it is quite complex.

Sub FilterExample_3()
Dim ws As Worksheet
Dim strFilter As String
Dim i As Long

Set ws = Worksheets("Sheet1")

With ws
'Test that AutoFilter is turned on
'(If turned off remaining code errors)
If .AutoFilterMode Then
With .AutoFilter
For i = 1 To .Filters.Count
'Test if filter is on
If .Filters(i).On Then
'Assign filter value to variable
strFilter = .Filters(i).Criteria1
If UCase(strFilter) = "=C" Then
'Turn off filter i
.Range.AutoFilter Field:=i
End If
End If
Next i
End With
Else
MsgBox "Autofilter on sheet " & _
ws.Name & " is turned off." & vbLf _
& "Processing terminated."
Exit Sub
End If
End With

End Sub



--
Regards,

OssieMac


"stickandrock" wrote:

I have an vb script that I run that formats several simular worksheets. One
of the things that is does is turns on the AutoFilter. I would like to have
it un-select one of the values on one of the columns during this format
process.

Example Data
a b
Row1 Ind Value
Row2 C 100
Row3 B 222
Row4 space 50
Row5 D 25
Row6 E 500

Both columns have the Filter On and I want to Un-Select all the values = "C"
in Column A

I know through recording a macro and looking at the code you can set a value
to a selection, but that only works if your selection is always in the same
position in the list. I do not have this option because some of my sheets
may have 2 options while others may have 20. To complicate things further
Value "C" may not even be in the list at all.

Thanks

I was not sure of exactly what you wanted. Anyway the code I gave you tests
all columns in the AutoFilter range. The following just tests column G.

I have assigned the column number of G to a variable. However, you need to
realize that this only works if the Autofilter columns start at column A
because the number required in the code is the number of the column in
Autofilter; not necessarily the worksheet column number. If say you have say
3 blank columns to the left of the autofiltered data then column 7 of the
Autofiltered data would not match column 7 of the worksheet and you would
need to specifically assign the autofilter column number to the variable.

As per my earlier comment, feel free to get back to me again if still not
what you want.

Sub FilterExample_4()
Dim ws As Worksheet
Dim strFilter As String
Dim colNumber As Long

'Convert column alpha Id to numeric
'Assumes that Autofilter columns start
'at column A.
colNumber = Columns("G").Column

Set ws = Worksheets("Sheet1")

With ws
'Test that AutoFilter is turned on
'(If turned off remaining code errors)
If .AutoFilterMode Then
'Test if filter colNumber is applied
With .AutoFilter
If .Filters(colNumber).On Then
'Assign filter value to variable
strFilter = .Filters(colNumber).Criteria1
If UCase(strFilter) = "=C" Then
'Turn off filter colNumber
.Range.AutoFilter Field:=colNumber
End If
End If
End With
Else
MsgBox "Autofilter is not turned on"
End If
End With

End Sub

--
Regards,

OssieMac

Thanks for all the help... I simplied the code a bit... No need to have the
extra error checks at this time...

Here were the resulting lines....

colNumber = Columns("D").Column
ActiveSheet.Cells.AutoFilter field:=colNumber, Criteria1:="<C"

Thank you for the education. I have just earned one brain cell back.

Don

"OssieMac" wrote:

I was not sure of exactly what you wanted. Anyway the code I gave you tests
all columns in the AutoFilter range. The following just tests column G.

I have assigned the column number of G to a variable. However, you need to
realize that this only works if the Autofilter columns start at column A
because the number required in the code is the number of the column in
Autofilter; not necessarily the worksheet column number. If say you have say
3 blank columns to the left of the autofiltered data then column 7 of the
Autofiltered data would not match column 7 of the worksheet and you would
need to specifically assign the autofilter column number to the variable.

As per my earlier comment, feel free to get back to me again if still not
what you want.

Sub FilterExample_4()
Dim ws As Worksheet
Dim strFilter As String
Dim colNumber As Long

'Convert column alpha Id to numeric
'Assumes that Autofilter columns start
'at column A.
colNumber = Columns("G").Column

Set ws = Worksheets("Sheet1")

With ws
'Test that AutoFilter is turned on
'(If turned off remaining code errors)
If .AutoFilterMode Then
'Test if filter colNumber is applied
With .AutoFilter
If .Filters(colNumber).On Then
'Assign filter value to variable
strFilter = .Filters(colNumber).Criteria1
If UCase(strFilter) = "=C" Then
'Turn off filter colNumber
.Range.AutoFilter Field:=colNumber
End If
End If
End With
Else
MsgBox "Autofilter is not turned on"
End If
End With

End Sub

--
Regards,

OssieMac