ExcelBanter - Creteria Help

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Creteria Help (https://www.excelbanter.com/excel-programming/384796-creteria-help.html)

Hi,

Thanks in advance

The following code is set for Creteria1 to = "A" how do I make it for A, B
or C

With Worksheets("Panel Details").Range("A1:IV2500")
.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With

Thanks again

Trev

Trev,
Maybe this will help:
http://www.meadinkent.co.uk/xlfilter.htm

Record a macro whilst you do this to generate the code.

NickHK

"Trever B" wrote in message
...
Hi,

Thanks in advance

The following code is set for Creteria1 to = "A" how do I make it for A, B
or C

With Worksheets("Panel Details").Range("A1:IV2500")
.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With

Thanks again

Trev

Thanks Nick,

Not much help

It does not help me how to input multi creteria,

Have tried:-
1) = "A" and "B" and "C"
2) = "A" or "B" or "C"

Tried recording a macro but still need help on criteria

Don't know what else to try.

Hope someone else can help.

Trev

"NickHK" wrote:

Trev,
Maybe this will help:
http://www.meadinkent.co.uk/xlfilter.htm

Record a macro whilst you do this to generate the code.

NickHK

"Trever B" wrote in message
...
Hi,

Thanks in advance

The following code is set for Creteria1 to = "A" how do I make it for A, B
or C

With Worksheets("Panel Details").Range("A1:IV2500")
.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With

Thanks again

Trev

Did you read this section ?
<Quote
a single item (e.g. 'SMITH') underneath one of the fields
more than one criteria item in a single row - applies an 'AND' rule (e.g.
'SMITH' and 'XN41')
more than one criteria on different rows - applies an 'OR' rule (e.g. 'XN41'
or '<1500')
</Quote
So you have to create your criteria in a column of
A
B
C

NickHK

"Trever B" wrote in message
...
Thanks Nick,

Not much help

It does not help me how to input multi creteria,

Have tried:-
1) = "A" and "B" and "C"
2) = "A" or "B" or "C"

Tried recording a macro but still need help on criteria

Don't know what else to try.

Hope someone else can help.

Trev

"NickHK" wrote:

Trev,
Maybe this will help:
http://www.meadinkent.co.uk/xlfilter.htm

Record a macro whilst you do this to generate the code.

NickHK

"Trever B" wrote in message
...
Hi,

Thanks in advance

The following code is set for Creteria1 to = "A" how do I make it for
A, B
or C

With Worksheets("Panel Details").Range("A1:IV2500")
.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With

Thanks again

Trev

Hi
Autofilter only lets you filter on two criteria at most. For 3
criteria you need advanced filter.
I'll assume you are selecting your criteria from a multiselect
listbox. If not, this should give you a start

Private Sub OKButton_Click()
Dim Grouplistdata As New Collection
Dim i As Integer, GroupCount As Integer
Dim Item As Variant
Dim GroupCriteria As Range

Application.ScreenUpdating = False
With RegGroups.GroupList
'Get the data in the GroupList ListBox
For i = 0 To .ListCount - 1
'for each Group in the list
If .Selected(i) Then
Grouplistdata.Add .List(i)
End If
Next i
End With

GroupCount = Grouplistdata.Count
'Remove any filter present
With Worksheets("Panel Details")
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
End With
Select Case GroupCount
'Check the user hasn't clicked OK on the ViewList userform without
selecting anything
Case 0
Exit Sub 'nothing happens and form is still visible
'Use AutoFilter if GroupCount is 1 or 2
Case 1
Worksheets("Panel Details").Range("A1:IV2500").AutoFilter
Field:=1, Criteria1:=CStr(Grouplistdata(1))
Case 2
Worksheets("Panel Details").Range("A1:IV2500").AutoFilter
Field:=1, Criteria1:=CStr(Grouplistdata(1)), Operator:=xlOr,
Criteria2:=CStr(Grouplistdata(2))
Case Is 2
'Start by inserting a worksheet and creating a Criteria Range to put
into AdvancedFilter
'addSheet first removes the sheet, so it is always a fresh sheet
AddSheet "WorkSpaceSheet" 'now the active sheet
With ActiveWorkbook.Worksheets("WorkSpaceSheet")
.Visible = False
.Cells(1, 1).Value = "Group" 'same as heading on worksheet
for "A", "B", "C",..
i = 1 'set a counter and put group items below "Group"
For Each Item In Grouplistdata
i = i + 1
.Cells(i, 1).Value = Item
Next Item

Set GroupCriteria = .Cells(1, 1).CurrentRegion
End With 'worksheet
'Filter the list
With Worksheets("Panel Details")
.Activate
.Range("A1:IV2500").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:=GroupCriteria
End with
End Select
End Sub

regards
Paul

Yes I did read the section but still did not like it and still no joy.

However Paul Robinson seams to have the answer

Thanks anyway

Regards

Trev

"NickHK" wrote:

Did you read this section ?
<Quote
a single item (e.g. 'SMITH') underneath one of the fields
more than one criteria item in a single row - applies an 'AND' rule (e.g.
'SMITH' and 'XN41')
more than one criteria on different rows - applies an 'OR' rule (e.g. 'XN41'
or '<1500')
</Quote
So you have to create your criteria in a column of
A
B
C

NickHK

"Trever B" wrote in message
...
Thanks Nick,

Not much help

It does not help me how to input multi creteria,

Have tried:-
1) = "A" and "B" and "C"
2) = "A" or "B" or "C"

Tried recording a macro but still need help on criteria

Don't know what else to try.

Hope someone else can help.

Trev

"NickHK" wrote:

Trev,
Maybe this will help:
http://www.meadinkent.co.uk/xlfilter.htm

Record a macro whilst you do this to generate the code.

NickHK

"Trever B" wrote in message
...
Hi,

Thanks in advance

The following code is set for Creteria1 to = "A" how do I make it for
A, B
or C

With Worksheets("Panel Details").Range("A1:IV2500")
.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With

Thanks again

Trev

Thanks Paul

Works a treat

Trev

" wrote:

Hi
Autofilter only lets you filter on two criteria at most. For 3
criteria you need advanced filter.
I'll assume you are selecting your criteria from a multiselect
listbox. If not, this should give you a start

Private Sub OKButton_Click()
Dim Grouplistdata As New Collection
Dim i As Integer, GroupCount As Integer
Dim Item As Variant
Dim GroupCriteria As Range

Application.ScreenUpdating = False
With RegGroups.GroupList
'Get the data in the GroupList ListBox
For i = 0 To .ListCount - 1
'for each Group in the list
If .Selected(i) Then
Grouplistdata.Add .List(i)
End If
Next i
End With

GroupCount = Grouplistdata.Count
'Remove any filter present
With Worksheets("Panel Details")
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
End With
Select Case GroupCount
'Check the user hasn't clicked OK on the ViewList userform without
selecting anything
Case 0
Exit Sub 'nothing happens and form is still visible
'Use AutoFilter if GroupCount is 1 or 2
Case 1
Worksheets("Panel Details").Range("A1:IV2500").AutoFilter
Field:=1, Criteria1:=CStr(Grouplistdata(1))
Case 2
Worksheets("Panel Details").Range("A1:IV2500").AutoFilter
Field:=1, Criteria1:=CStr(Grouplistdata(1)), Operator:=xlOr,
Criteria2:=CStr(Grouplistdata(2))
Case Is 2
'Start by inserting a worksheet and creating a Criteria Range to put
into AdvancedFilter
'addSheet first removes the sheet, so it is always a fresh sheet
AddSheet "WorkSpaceSheet" 'now the active sheet
With ActiveWorkbook.Worksheets("WorkSpaceSheet")
.Visible = False
.Cells(1, 1).Value = "Group" 'same as heading on worksheet
for "A", "B", "C",..
i = 1 'set a counter and put group items below "Group"
For Each Item In Grouplistdata
i = i + 1
.Cells(i, 1).Value = Item
Next Item

Set GroupCriteria = .Cells(1, 1).CurrentRegion
End With 'worksheet
'Filter the list
With Worksheets("Panel Details")
.Activate
.Range("A1:IV2500").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:=GroupCriteria
End with
End Select
End Sub

regards
Paul

Hi
I forgot to insert the AddSheet macro, so it did well to work!

Public Sub AddSheet(TheSheetName As String)
Dim wsNew As Worksheet 'we will insert a fresh worksheet
With ActiveWorkbook
On Error Resume Next
Application.DisplayAlerts = False
.Worksheets(TheSheetName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
'If worksheet is not there the error in deletion is ignored
'Add a new sheet
Set wsNew = .Worksheets.Add(after:=Worksheets("Panel Details"))
'Makes wsNew the active sheet
wsNew.Name = TheSheetName
End With
Set wsNew = Nothing
End Sub

regards
Paul