Filter on more than one field

B

beancurdjelly2003

Hi All,

Who can tell me the command of auto filter on more than one field? I
read Ron's example (http://www.rondebruin.nl/copy5.htm#sheet) Create a
new sheet for one unique value(AutoFilter) and found that Tip's on
Filter on more than one field, but I still don't understand. Because
his example already knew the Criteria, how about if don't know the
criteria? Only want to setup 2 field and copy all unique record? I
can do it only one filter (rng.AutoFilter Field:=FieldNum,
Criteria1:="=" & cell.Value), what's code for two filter?

Ron's example:
rng.AutoFilter Field:=1, Criteria1:="=Netherlands"
rng.AutoFilter Field:=3, Criteria1:="=F"

And how can I change the command, if the header is not on the first
row (e.g. the third row) and need to copy unique record include the
first row and next to the table empty row information to new
workbook. Below is my example, I need to split by dep't with
different approval to other workbook. My data on dept over 100's depts
and each of dept have more than 2 different approval. So, I need to
split many many file, pls help me.

Below is orignal file:

Account No: 12345678
Inv date: 19/03/08
Dep't Approval header3 header4
AC James
AC James
HR James
HR Kwok
HR Kwok

information xxx xxxxx

Output file should be look like as below:

Account No: 12345678
Inv date: 19/03/08
Dep't Approval header3 header4
HR Kwok
HR Kwok

Another output file as below:

Account No: 12345678
Inv date: 19/03/08
Dep't Approval header3 header4
HR James
 
B

beancurdjelly2003

Hi All,

Who can tell me the command of auto filter on more than one field? I
read Ron's example (http://www.rondebruin.nl/copy5.htm#sheet) Create a
new sheet for one unique value(AutoFilter) and found that Tip's on
Filter on more than one field, but I still don't understand. Because
his example already knew the Criteria, how about if don't know the
criteria? Only want to setup 2 field and copy all unique record? I
can do it only one filter (rng.AutoFilter Field:=FieldNum,
Criteria1:="=" & cell.Value), what's code for two filter?

Ron's example:
rng.AutoFilter Field:=1, Criteria1:="=Netherlands"
rng.AutoFilter Field:=3, Criteria1:="=F"

And how can I change the command, if the header is not on the first
row (e.g. the third row) and need to copy unique record include the
first row and next to the table empty row information to new
workbook. Below is my example, I need to split by dep't with
different approval to other workbook. My data on dept over 100's depts
and each of dept have more than 2 different approval. So, I need to
split many many file, pls help me.

Below is orignal file:

Account No: 12345678
Inv date: 19/03/08
Dep't Approval header3 header4
AC James
AC James
HR James
HR Kwok
HR Kwok

information xxx xxxxx

Output file should be look like as below:

Account No: 12345678
Inv date: 19/03/08
Dep't Approval header3 header4
HR Kwok
HR Kwok
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

setting more than one filter 1
Triple Filter? Ron de Bruin Code. 1
Using Ron's Sub Copy_to_Workbooks 6
Autofil on variable rows, delete extract and show remaining rows 3
Copy row from one sheet to one of many others 6
Feed List of Names into Ron de Bruin Code-Filter 6
Custom Filter using more than two 'OR' 5
Runs once and only once 1

Top