Advance Filter

[RKS]

Hi All,
PLease help me.
I Have a Data sheet which have 200 record which we protect and deliverd to
user and have A to I column.
I can another Product List where data comes from datasheet with one critera.
I can take help from
http://www.contextures.com/excelfiles.html under Filters, for 'FL0002 -
which is 95% same as my requirement.
I have only one problem. in my data sheet have A to I column and In product
sheet I need only selected column
like A,B,D,F,G only. I can show which code i use.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 3 Then
'calculate criteria cell in case calculation mode is manual
Sheets("ProductsList").Range("Criteria").Calculate
Worksheets("ProductsList").Range("Database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("ProductsList").Range("Criteria"), _
CopyToRange:=Range("A6:I6"), Unique:=False
'calculate summary total in case calculation mode is manual
Sheets("Data Entry").Range("D2").Calculate
End If
End Sub

Exmaple Data Sheet Product
sheet (after advance filter)
Column A B C D E F G H I A
B D F G

Thanks
RKS

 

[Debra Dalgleish]

As answered in microsoft.public.excel.misc:

Instead of including all the column headings in the extract area, just
display the headings for the A, C and F columns.
Then, change the code so it refers to those headings, e.g.
CopyToRange:=Range("A6:C6"), Unique:=False

 

[RKS]

Hi Debra
Please change my code so i can understand what change need. I need only A,C
and F column. i can try many time but not success. so please change so i can
copy only selected column.

Thanks
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 3 Then
'calculate criteria cell in case calculation mode is manual
Sheets("ProductsList").Range("Criteria").Calculate
Worksheets("ProductsList").Range("Database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("ProductsList").Range("Criteria"), _
CopyToRange:=Range("A6:I6"), Unique:=False
'calculate summary total in case calculation mode is manual
Sheets("Data Entry").Range("D2").Calculate
End If
End Sub

If u not notunderstand please see http://www.contextures.com/excelfiles.html
under Filters, for 'FL0002 - example.

Thanks
RKS

 

[Debra Dalgleish]

First, you have to set up the Extract range with the column headings for
A, C and F.
Also, set up and name the criteria range. There are instructions here:

http://www.contextures.com/xladvfilter01.html

Then, change the sheet names in the code, to match the sheet names in
your workbook.
In the sample file, the text is entered in cell C2 (row 2, column 3)
If your entry cell is different, change the target row and target column
in the code.

 

[RKS]

Hi Debra
Thanks for reply. but can't understand what u say. please show me after
change in example. so i can filter only 3 column like A,C and F. please show
me criteria, Extract etc. after change which u say me. my purpose only select
only particular column which i wants not all column range.
waiting your reply,

 

[Debra Dalgleish]

You have to set up your worksheet before you change the code. As I
suggested earlier, read the information on Advanced Filters here:
http://www.contextures.com/xladvfilter01.html

It shows how to set up the extract range with only some of the headings
from the product table.

In my first reply, I said to change the CopyTo reference in the code, so
it's only 3 cells:
CopyToRange:=Range("A6:C6"), Unique:=False