PDA

View Full Version : Filter Problems



CatDaddy
06-29-2011, 03:19 PM
all of the filters work except for the <> ones :(

ActiveSheet.Range("A1").Select
Selection.AutoFilter
'Filter Month
ActiveSheet.Range("A1").AutoFilter Field:=37, Criteria1:=">=" & MonStart, _
Operator:=xlAnd, Criteria2:="<=" & MonEnd
'Filter out feild 6, order status
ActiveSheet.Range("A1").AutoFilter _
Field:=6, _
Criteria1:=Array("<>MARK FOR DELETE", "<>Needs VIP Pre Call", _
"<>Order Cancelled", "<>Follow Up Needed"), _
Operator:=xlOr
'Filter out feild 4, type of order
ActiveSheet.Range("A1").AutoFilter _
Field:=4, _
Criteria1:="New", _
Operator:=xlFilterValues

'Filter out feild 5, type of order
ActiveSheet.Range("A1").AutoFilter _
Field:=5, _
Criteria1:="<>Trial", _
Operator:=xlOr, _
Criteria2:="<>Equipment Swap"
'Filter out feild 19, model
ActiveSheet.Range("A1").AutoFilter _
Field:=19, _
Criteria1:="<>HD Surrender", _
Operator:=xlFilterValues


when i look at the filter dropdowns it seems to be trying to filter out the right <> values, but for the criteria aray and the xlOr (trial/equip swap) it is only trying (unsuccessfully) to filter the last value

...thanks in advance

CatDaddy
06-29-2011, 04:10 PM
after further research i have seen that perhaps you cannot use <> in a criteria array, however i tried this workaround and it still is not working...

Dim testcell As Range
Dim fVRow As Long
Set testcell = ActiveSheet.Range("A2")
While testcell.Rows.Hidden = True
Set testcell = testcell.Offset(1, 0)
Wend
fVRow = testcell.Row


'Filter out feild 6, order status

Dim fltrdRng As Range, Dn As Range
Dim orderStatArr() As Variant

Set fltrdRng = ActiveSheet.Range("F" & fVRow & ":F" & lRow).Cells(xlCellTypeVisible)

orderStatArr = Array("MARK FOR DELETE", "Needs VIP Pre Call", _
"Order Cancelled", "Follow Up Needed")

For Each Dn In fltrdRng
For n = 0 To 3
If Dn.Text = orderStatArr(n) Then
Dn.EntireRow.Hidden = True
Exit For
End If
Next n
Next Dn

frank_m
06-29-2011, 08:32 PM
does the code at this link help?
http://vbadud.blogspot.com/2010/07/excel-vba-autofilter-specify-multiple.html

This first method I ran without error, but I didn't use sample data that would allow me to analyze the results

'partial sample's derived from link given above
Dim arCriteria(0 To 1) As String

arCriteria(0) = "MARK FOR DELETE"
arCriteria(1) = "Follow Up Needed"

ActiveSheet.UsedRange.AutoFilter Field:=6, Criteria1:="<> arCriteria", Operator:=xlAnd
this other method gave a type mismatch error
ActiveSheet.AutoFilter Field:=6, Criteria1:="<>" _
& Array("MARK FOR DELETE", "MARK FOR DELETE"), Operator:=xlFilterValues

CatDaddy
06-30-2011, 11:57 AM
does the code at this link help?
http://vbadud.blogspot.com/2010/07/excel-vba-autofilter-specify-multiple.html

This first method I ran without error, but I didn't use sample data that would allow me to analyze the results

'partial sample's derived from link given above
Dim arCriteria(0 To 1) As String

arCriteria(0) = "MARK FOR DELETE"
arCriteria(1) = "Follow Up Needed"

ActiveSheet.UsedRange.AutoFilter Field:=6, Criteria1:="<> arCriteria", Operator:=xlAnd
this other method gave a type mismatch error
ActiveSheet.AutoFilter Field:=6, Criteria1:="<>" _
& Array("MARK FOR DELETE", "MARK FOR DELETE"), Operator:=xlFilterValues


see im with you on that, the problem seems to be that excel filter cant take more than <> not criteria...

Paul_Hossler
06-30-2011, 07:20 PM
Interesting. I didn't know about that option with filters

I think the xlFilterValues is theres more than 2 values to include, and seems to be the VBA equivalnet of checking more than 2 boxes


ActiveSheet.Range("$A$1:$C$19").AutoFilter Field:=1, Criteria1:=Array("A", "F", "S"), Operator:=xlFilterValues


So seems to me that you'd be limited to only specifiying the values to include, not exclude. Unless you build an array of all the values in the range, less the ones to exclude

Paul

CatDaddy
07-01-2011, 10:50 AM
Interesting. I didn't know about that option with filters

I think the xlFilterValues is theres more than 2 values to include, and seems to be the VBA equivalnet of checking more than 2 boxes


ActiveSheet.Range("$A$1:$C$19").AutoFilter Field:=1, Criteria1:=Array("A", "F", "S"), Operator:=xlFilterValues


So seems to me that you'd be limited to only specifiying the values to include, not exclude. Unless you build an array of all the values in the range, less the ones to exclude

Paul

ya the cool thing about this problem is that the data to remove changes constantly but the data to remove always have a few key values...what i ended up doing is copying the data to filter to a new WS and filter the values i want to keep and delete the rest...it works, but i wish there were a cooler work-around