Apply filter on the basis of multiple values

Tejas Kore

Board Regular
Joined
Nov 2, 2017
Messages
57
I have this data .
CountryName
IndiaA
IndiaB
IndiaC
AustraliaD
AustraliaE
PeruF
PeruG
ChileH
ChileI

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Now I want to apply filter to this data in such a way that India And Australia comes in one category and Peru And Chile comes in another.
Right now I have the code to split this file in 4 files , each one corresponding to one country.
How can I apply multiple search criteria to Column A which is the column containing Countries ?

Here is the code :

Sub Sample()


Dim wswb As String
Dim wssh As String


wswb = ActiveWorkbook.Name
wssh = ActiveSheet.Name


vcolumn = InputBox("Please indicate which column,you would like to split by", "Column Selection")
Columns(vcolumn).Copy
Sheets.Add
ActiveSheet.Name = "_Summary"
Range("A1").PasteSpecial
Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes
vCounter = Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To vCounter
vfilter = Sheets("_Summary").Cells(i, 1)
Sheets(wssh).Activate
ActiveSheet.Columns.AutoFilter field:=Columns(vcolumn).Column, Criteria1:=vfilter
Cells.Copy
Workbooks.Add
Range("A1").PasteSpecial
If vfilter <> "" Then
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\split results" & vfilter
Else
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\split results\_Empty"
End If
ActiveWorkbook.Close
Workbooks(wswb).Activate


Next i


Cells.AutoFilter
Application.DisplayAlerts = False
Sheets("_Summary").Delete


End Sub
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
Code:
Sub F()
    With Range("A1").CurrentRegion
        .AutoFilter Field:=1, Criteria1:=Array("India", "Australia"), Operator:=xlFilterValues
        .Copy Sheets.Add().Cells(1)
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,089,969
Messages
5,411,571
Members
403,380
Latest member
ifog671

This Week's Hot Topics

Top