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" |
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 |
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" |
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" |
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" |
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" |
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" |
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" |
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" |
All times are GMT +1. The time now is 02:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com