limited filter criteria

[dalerguy]

Hi,

I noticed that when using autofilter you can only use 2 criteria with a statement such as

copyRng.Autofilter Field:=7, Criteria1:="Wrong Num", Operator:=xlOr, Criteria2:="Disconnect"

I have a column (Field7) which I'm organizing the data by 5 criteria. Basically if you have value 1 or 2 or 4 copy to sheet 1, if you have value 3 copy to sheet2, if you have value 5 copy to sheet 3.

How can I use autofilter with 5 criteria?

Thanks

 

[Von Pookie]

Since each number is going to a different sheet, you can use a loop to filter for each item, in turn. Completely untested, but worth a shot:

Sub test()
Dim DataRng As Range, PasteSheet As Worksheet
Dim i As Integer

'range of data--not including header row (row 1)
Set DataRng = Range("G2", Range("G65536").End(xlUp))

Application.ScreenUpdating = False
With Rows(1)
    .AutoFilter 'turn on autofilter on row 1
    For i = 1 To 5 'where numbers 1-5 are your criteria
        .AutoFilter field:=7, Criteria1:=i 'filter column G
        Select Case i
            Case Is = 1, 2, 4 'if current criteria is 1, 2 or 4, data goes to Sheet1
                Set PasteSheet = Sheets("Sheet1")
            Case Is = 3 'if current criteria is 3, data goes to Sheet2
                Set PasteSheet = Sheets("Sheet2")
            Case Is = 5 'if current criteria is 5, data goes to Sheet3
                Set PasteSheet = Sheets("Sheet3")
        End Select
                
        'copy data visible in column G, paste to corresponding sheet
        DataRng.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=PasteSheet.Range("A65536").End(xlUp).Offset(1, 0)
    Next i
    .AutoFilter 'turn off autofilter
End With
Application.ScreenUpdating = True

end Sub

 

[dalerguy]

Below is what i did with the code you given me. I'm a getting an error " No list was found, select a single cell within list and run command again "

VBA editor is hightlighing this line of code: .AutoFilter 'turn on autofilter on row 1

A change from my original post is the criteria are now decesased, wrong num, etc... not number 1 to 5 as I indicated. Could this be the problem?

code____________________________________________

Private Sub test()
Dim DataRng As Range, PasteSheet As Worksheet
Dim i As Integer

'range of data--not including header row (row 1)
Set DataRng = Range("A2", Range("A65536").End(xlUp))

Application.ScreenUpdating = False
With Rows(1)
.AutoFilter 'turn on autofilter on row 1
For i = 1 To 5 'where numbers 1-5 are your criteria
.AutoFilter field:=9, Criteria1:=i 'filter column 9
Select Case i
Case Is = "Deceased", "Disconnect", "Wrong Num", "Remove Lst", "DoNot Call"
Set PasteSheet = Sheets("alumni_records")
Case Is = "No English", "Out Cntry", "Already Pl", "Yes Pledge", "Maybe Pledge", "No Pldge" 'if current criteria is 3, data goes to Sheet2
Set PasteSheet = Sheets("supervisor_review")

End Select

'copy data visible in column G, paste to corresponding sheet
DataRng.SpecialCells(xlCellTypeVisible).Copy _
Destination:=PasteSheet.Range("A65536").End(xlUp).Offset(1, 0)
Next i
.AutoFilter 'turn off autofilter
End With
Application.ScreenUpdating = True

End Sub

 

[Von Pookie]

Do you have any data in row 1? The code I posted is assuming there are column headers in row 1, and the data in row 2 and onward.

Also, you'll need to change the criteria, yes. The code is currently filtering for the numbers 1-5, as that was what you gave as your example. You could work an array into this instead, though:

Sub test()
Dim DataRng As Range, PasteSheet As Worksheet
Dim myCriteria As Variant, i As Integer

'range of data--not including header row (row 1)
Set DataRng = Range("G2", Range("G65536").End(xlUp))

'an array that holds the terms that will be filtered for
myCriteria = Array("Deceased", "Disconnect", _
"Wrong Num", "Remove Lst", "DoNot Call", "No English", _
"Out Cntry", "Already Pl", "Yes Pledge", "Maybe Pledge", "No Pldge")

Application.ScreenUpdating = False
With Rows(1)
    .AutoFilter 'turn on autofilter on row 1
    For i = LBound(myCriteria) To UBound(myCriteria)
        .AutoFilter field:=7, Criteria1:=myCriteria(i) 'filter column G
        Select Case myCriteria(i)
            Case Is = "Deceased", "Disconnect", _
            "Wrong Num", "Remove Lst", "DoNot Call"
                Set PasteSheet = Sheets("alumni_records")
            Case Is = "No English", "Out Cntry", "Already Pl", _
            "Yes Pledge", "Maybe Pledge", "No Pldge"
                Set PasteSheet = Sheets("supervisor_review")
        End Select
               
        'copy data visible in column G, paste to corresponding sheet
        DataRng.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=PasteSheet.Range("A65536").End(xlUp).Offset(1, 0)
    Next i
    .AutoFilter 'turn off autofilter
End With
Application.ScreenUpdating = True

End Sub

This is completely untested, but should give you an idea. Again, this is assuming the header row is row 1, and data starts in row 2.

 

[dalerguy]

Yes row 1 is column headings and data starts at A2. I see how that uses the array. Thanks.

When run I still get the error " No list was found, select a single cell within list and run command again" on this line of code:
.AutoFilter 'turn on autofilter on row 1

 

[Von Pookie]

Are you on the correct sheet? As it is written right now, the code will (attempt to) run on whatever worksheet is currently active.

 

[dalerguy]

It wasn't on the correct sheet. I fixed that. i'm getting closer.

I'm getting a No data error:

DataRng.SpecialCells(xlCellTypeVisible).Copy _
Destination:=PasteSheet.Range("A65536").End(xlUp).Offset(1, 0)

this is what is happening on the workbook. Sheet1 contains the data. Filter seems to pick the proper records and but only pastes Column A data in alumni_records sheet and supervisor sheet. All other columns are not pasted.

 

[Von Pookie]

You'll need to change the DataRng if you want to copy more columns. This line is where it sets the DataRng variable:

Set DataRng = Range("G2", Range("G65536").End(xlUp))

Right now, it is only looking at column G, so it is doing just what it's programmed to. Change this to whatever your range is. So for example, if the first column of data is A and the last column is K, you could use:

Set DataRng = Range("A2", Range("K65536").End(xlUp))

Then when it filters column G, it will copy the cells within the area set to the DataRng variable that are visible.

Also:

I'm getting a No data error:

DataRng.SpecialCells(xlCellTypeVisible).Copy _
Destination:=PasteSheet.Range("A65536").End(xlUp).Offset(1, 0)

That's kind of my fault, there. The code is telling it to copy the visible cells within DataRng. If there are no visible rows within the range after the filter, there is nothing to copy and you'll see that error.

Add

On Error Resume Next

as the line right before that, and it should skip over that if it errors out on the copy and just go on to the next criteria.

 

[dalerguy]

Ok, made that change.

Result: row 1 (column headings) was pasted to each alumni_records and supervisor sheet into the first 6 rows. That's odd since the DataRng = Range("A2", Range("M65536").End(xlUp))

Does this paste cold have to be changed due to the Range adjustment?
DataRng.SpecialCells(xlCellTypeVisible).Copy _
Destination:=PasteSheet.Range("A65536").End(xlUp).Offset(1, 0)



Thanks for being patient!

 

[Von Pookie]

Hmm. Well, the destination of the copied cells should be fine, but I'm not sure what's going on with the headers being copied.

Can you post a small sample of the sheet? Maybe seeing how you have things set up will help.