Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - 3 conditions
Hi There,
I have written a macro that selects from column D, using autofilter TWO sets of data and copies and pastes it into a new book. Problem is I now need to filter data for three conditions, ie condition 1 or condition 2 or condition 3. Excel autofilter only seems to have to custom filters... Im sure it can be done in VB.... My original code is below, any help graciously accepted... Thanks D Sub test() With Sheets("a").Range("A1:e150") .AutoFilter Field:=4, Criteria1:="1", Operator:=xlOr, _ Criteria2:="=2" .SpecialCells(xlCellTypeVisible).Copy _ Sheets.Add ActiveSheet.Paste Cells.Select Cells.EntireColumn.AutoFit Cells.Select Selection.RowHeight = 15 ActiveWindow.Zoom = 80 ActiveSheet.Name = "a_new" 'Now remove the autofilter .AutoFilter End With End Sub *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - 3 conditions
Try this Add-in Darin
http://www.rondebruin.nl/easyfilter.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Darin Kramer" wrote in message ... Hi There, I have written a macro that selects from column D, using autofilter TWO sets of data and copies and pastes it into a new book. Problem is I now need to filter data for three conditions, ie condition 1 or condition 2 or condition 3. Excel autofilter only seems to have to custom filters... Im sure it can be done in VB.... My original code is below, any help graciously accepted... Thanks D Sub test() With Sheets("a").Range("A1:e150") .AutoFilter Field:=4, Criteria1:="1", Operator:=xlOr, _ Criteria2:="=2" .SpecialCells(xlCellTypeVisible).Copy _ Sheets.Add ActiveSheet.Paste Cells.Select Cells.EntireColumn.AutoFit Cells.Select Selection.RowHeight = 15 ActiveWindow.Zoom = 80 ActiveSheet.Name = "a_new" 'Now remove the autofilter .AutoFilter End With End Sub *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - 3 conditions
suggestion: filter a helper column counting conditions met. (ie: if
condition1 true Col(z)=1, +1 if condition2 also true, +1 if condition3 also true, etc then autofilter col(z)# of conditions met)) "Darin Kramer" wrote in message ... Hi There, I have written a macro that selects from column D, using autofilter TWO sets of data and copies and pastes it into a new book. Problem is I now need to filter data for three conditions, ie condition 1 or condition 2 or condition 3. Excel autofilter only seems to have to custom filters... Im sure it can be done in VB.... My original code is below, any help graciously accepted... Thanks D Sub test() With Sheets("a").Range("A1:e150") .AutoFilter Field:=4, Criteria1:="1", Operator:=xlOr, _ Criteria2:="=2" .SpecialCells(xlCellTypeVisible).Copy _ Sheets.Add ActiveSheet.Paste Cells.Select Cells.EntireColumn.AutoFit Cells.Select Selection.RowHeight = 15 ActiveWindow.Zoom = 80 ActiveSheet.Name = "a_new" 'Now remove the autofilter .AutoFilter End With End Sub *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - 3 conditions
Hi Ron,
Good solution, but ... unfortunately it has to run on an unknown number of users machines, so I cant install an add in on each of their machines.... *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - 3 conditions
Hi Darin
You can use Advanced Filter to do this See Debra's site for examples http://www.contextures.com/tiptech.html -- Regards Ron de Bruin http://www.rondebruin.nl "Darin Kramer" wrote in message ... Hi Ron, Good solution, but ... unfortunately it has to run on an unknown number of users machines, so I cant install an add in on each of their machines.... *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - 3 conditions
I need to run a Macro to do this, because users who need to use the file
do not know how to run an advanced autofilter. One Macro is running to clean up all the data, and the sort according to three condtions is one part of the Macro. ie, I need the VBA to be able to sort via the three conditions... Thanks D :) *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
Can I use more than two conditions in custom autofilter? | Excel Worksheet Functions | |||
AutoFilter Custom Conditions | Excel Programming |