Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter or something like it
Shouldn't this work? This is part of a greater VB code. I will post it after
this one. Selection.AutoFilter Field:=2, Criteria1:="=GDL", Operator:=xlOr, _ Criteria2:="=MTY", Operator:=xlOr, Criteria3:="=QRO", Operator:=xlOr, _ Criteria4:="=SLW" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter or something like it
Sub A_1600()
Dim LastRow As Long Selection.AutoFilter Selection.AutoFilter Field:=2, Criteria1:="=GDL", Operator:=xlOr, _ Criteria2:="=MTY", Criteria3:="=QRO", Operator:=xlOr,_ Criteria4:="=SLW" Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy Workbooks.Add ActiveSheet.Paste Range("A1").Select Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("A1").Select Rows("1:1").Select Selection.ClearContents Columns("I:N").Select Selection.Delete Shift:=xlToLeft Range("A1").Select ActiveCell.FormulaR1C1 = "ORG" Range("B1").Select ActiveCell.FormulaR1C1 = "DSTN" Range("C1").Select ActiveCell.FormulaR1C1 = "BAX P O/N" Range("D1").Select ActiveCell.FormulaR1C1 = "227 kgs" Range("E1").Select ActiveCell.FormulaR1C1 = "454 kgs" Range("F1").Select ActiveCell.FormulaR1C1 = "BAX O/N" Range("G1").Select ActiveCell.FormulaR1C1 = "227 kgs" Range("H1").Select ActiveCell.FormulaR1C1 = "454 kgs" Range("I1").Select Selection.NumberFormat = "0.00" ActiveCell.FormulaR1C1 = "BAX 2 Day" Range("J1").Select Selection.NumberFormat = "0.00" ActiveCell.FormulaR1C1 = "227 kgs" Range("K1").Select Selection.NumberFormat = "0.00" ActiveCell.FormulaR1C1 = "454 kgs" Range("L1").Select Selection.NumberFormat = "0.00" ActiveCell.FormulaR1C1 = "BAX Ground" Range("M1").Select Selection.NumberFormat = "0.00" ActiveCell.FormulaR1C1 = "227 kgs" Range("N1").Select Selection.NumberFormat = "0.00" ActiveCell.FormulaR1C1 = "454 kgs" Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Selection.Font.ColorIndex = 2 With Selection.Interior .ColorIndex = 55 .Pattern = xlSolid End With Selection.Font.Bold = True With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlTop .WrapText = True .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With ActiveSheet LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row .Rows(LastRow + 1 & ":" & .Rows.Count).Delete End With Range("O2").Select ActiveCell.FormulaR1C1 = "2.2046" Range("O3").Select ActiveCell.FormulaR1C1 = "100" Range("O2").Select Selection.Copy Range("C:H", Range("C:H").End(xlDown)).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlMultiply, SkipBlanks _ :=False, Transpose:=False Range("O3").Select Selection.Copy Range("C:H", Range("C:H").End(xlDown)).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlDivide, SkipBlanks _ :=False, Transpose:=False Range("C:H", Range("C:H").End(xlDown)).Select Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)" With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Rows(LastRow + 1 & ":" & .Rows.Count).Delete End With Columns("O:O").Select Selection.Delete Shift:=xlToLeft Range("A2").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter or something like it
Hi pgarcia
The max = 2 criteria with AutoFilter You can use advancedfilter with your criteria in a range What do you want to do with the filter result -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pgarcia" wrote in message ... Shouldn't this work? This is part of a greater VB code. I will post it after this one. Selection.AutoFilter Field:=2, Criteria1:="=GDL", Operator:=xlOr, _ Criteria2:="=MTY", Operator:=xlOr, Criteria3:="=QRO", Operator:=xlOr, _ Criteria4:="=SLW" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter or something like it
Thanks again. Take a look at my 2nd posting and you'll see I what I'm up to.
"Ron de Bruin" wrote: Hi pgarcia The max = 2 criteria with AutoFilter You can use advancedfilter with your criteria in a range What do you want to do with the filter result -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pgarcia" wrote in message ... Shouldn't this work? This is part of a greater VB code. I will post it after this one. Selection.AutoFilter Field:=2, Criteria1:="=GDL", Operator:=xlOr, _ Criteria2:="=MTY", Operator:=xlOr, Criteria3:="=QRO", Operator:=xlOr, _ Criteria4:="=SLW" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter or something like it
Thanks, I used the Advanced Filter and that did the trick.
"Ron de Bruin" wrote: Hi pgarcia The max = 2 criteria with AutoFilter You can use advancedfilter with your criteria in a range What do you want to do with the filter result -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pgarcia" wrote in message ... Shouldn't this work? This is part of a greater VB code. I will post it after this one. Selection.AutoFilter Field:=2, Criteria1:="=GDL", Operator:=xlOr, _ Criteria2:="=MTY", Operator:=xlOr, Criteria3:="=QRO", Operator:=xlOr, _ Criteria4:="=SLW" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter or something like it
Try this without all the = after "
Selection.AutoFilter Field:=2, Criteria1:="GDL", Operator:=xlOr, _ Criteria2:="MTY", Operator:=xlOr, Criteria3:="QRO", Operator:=xlOr, _ Criteria4:="SLW" "pgarcia" wrote in message ... Shouldn't this work? This is part of a greater VB code. I will post it after this one. Selection.AutoFilter Field:=2, Criteria1:="=GDL", Operator:=xlOr, _ Criteria2:="=MTY", Operator:=xlOr, Criteria3:="=QRO", Operator:=xlOr, _ Criteria4:="=SLW" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter or something like it
More than two, you will need to enter as an array :-
Selection.AutoFilter Field:=11, Criteria1:=Array( _ "GDL", "MTY", "QRO", "SLW"), Operator:=xlFilterValues "pgarcia" wrote in message ... Shouldn't this work? This is part of a greater VB code. I will post it after this one. Selection.AutoFilter Field:=2, Criteria1:="=GDL", Operator:=xlOr, _ Criteria2:="=MTY", Operator:=xlOr, Criteria3:="=QRO", Operator:=xlOr, _ Criteria4:="=SLW" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter or something like it
Should read Field 2 not 11
Selection.AutoFilter Field:=2, Criteria1:=Array( _ "GDL", "MTY", "QRO", "SLW"), Operator:=xlFilterValues More than two, you will need to enter as an array :- Selection.AutoFilter Field:=11, Criteria1:=Array( _ "GDL", "MTY", "QRO", "SLW"), Operator:=xlFilterValues "pgarcia" wrote in message ... Shouldn't this work? This is part of a greater VB code. I will post it after this one. Selection.AutoFilter Field:=2, Criteria1:="=GDL", Operator:=xlOr, _ Criteria2:="=MTY", Operator:=xlOr, Criteria3:="=QRO", Operator:=xlOr, _ Criteria4:="=SLW" |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFilter or something like it
Thanks, I used the Advanced Filter and that did the trick.
"Skinman" wrote: Should read Field 2 not 11 Selection.AutoFilter Field:=2, Criteria1:=Array( _ "GDL", "MTY", "QRO", "SLW"), Operator:=xlFilterValues More than two, you will need to enter as an array :- Selection.AutoFilter Field:=11, Criteria1:=Array( _ "GDL", "MTY", "QRO", "SLW"), Operator:=xlFilterValues "pgarcia" wrote in message ... Shouldn't this work? This is part of a greater VB code. I will post it after this one. Selection.AutoFilter Field:=2, Criteria1:="=GDL", Operator:=xlOr, _ Criteria2:="=MTY", Operator:=xlOr, Criteria3:="=QRO", Operator:=xlOr, _ Criteria4:="=SLW" |
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) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |