Auto Filter in EXCEL 2007

[Guest]

hi all,
as in excel 2003 while using auto filter in a particular column we get the
drop down list box and the user has the choice of selecting and one from teh
whole list, but in excel 2007 while using auto filter option we get the drop
down list box with a checkbox prior to it allowing the user to select
multiple rows in the filter.

can the auto filter in teh excel 2007 can be modiifed to display teh data as
in the form of excel 2003 (ie without the check boxes in front of the list)

 

[Guest]

Not that I know of... which doesn't mean there isn't one... however, just
FYI, when you first open the list, while everything is still checked,
clicking the select all option will uncheck everything - making it easier to
select the one you want instead of having to uncheck all the ones you don't.

 

[Nick Hodge]

Pramod

No, but if the user clicks on 'Select all' (actually de-selects all) and
then on the single item they want. One extra click, but the functionality is
the same

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

 

[Guest]

hi boni,nick

thanks for the help.. but i have a bit different problem..
when the user selects two items from the list of the dropdownlist box say
(item1 and item4) and selects the colums with all the for those two
selections and press ctrl+c and opens a new work sheet and presses ctrl+v..
i want only those rows to be copied which matches the autofilter criteria....

one way is to do it with f5->special->visible cells only then ctrl+c and
then ctrl+v.. but i want that to be done in VBA code....

waiting 4 u replies..

and one more thing .. is there any way we can restrict the user from copying
anything from the excel.. i.e to disable ctrl+c option...

 

[Roger Govier]

Hi

If you want to copy the filtered rows, just select the visible range of
rows by marking the row number, then copy and paste.

If you want to do it by macro, then just switch on the macro recorder as
you carry out the task.
This is what was created for me when I did it.

I had already created a Table called MyData in XL2007, by going to the
Insert Tab>Table
Then under the Table Tools>Design tab>far left Properties>enter MyData
in the Table Name pane.

I selected 2 customers, called Smith and Jones, from the second dropdown
filter on column B

Sub CopyFilteredRows()

ActiveSheet.ListObjects("MyData").Range.AutoFilter Field:=2,
Criteria1 _
:="=Smith", Operator:=xlOr, Criteria2:="=Jones"
Rows("4:300").Select
Selection.Copy
Sheets("Sheet2").Select

Range("A4").Select
ActiveSheet.Paste
End Sub