criteriarange not equal to

  • Thread starter Thread starter webels
  • Start date Start date
W

webels

Hi
The following code works great for me
Cells.Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Test1"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:AL1"),
Type:=xlFillDefault
Range("A1:AL1").Select
Range("AR1").Select
ActiveCell.FormulaR1C1 = "Test10"
Range("AR2").Select
ActiveCell.FormulaR1C1 = "0"
Range("AR3").Select
ActiveCell.FormulaR1C1 = "neg"


Columns("J:J").Select
Range("J1:J500").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("AR1:AR3"), Unique:=False

However what would the syntax be I I wanted to fing all records not
equal to O and neg.
I have tried CriteriaRange:<> but to no avail.

Thanks
Eddie
 
Hi Eddie,

Am Mon, 20 Jun 2011 07:50:29 -0700 (PDT) schrieb webels:
However what would the syntax be I I wanted to fing all records not
equal to O and neg.
I have tried CriteriaRange:<> but to no avail.
Click to expand...

you want find all positive values?
Try this:
Rows("1:1").Insert Shift:=xlDown
Range("A1") = "Test1"
Range("A1").AutoFill Destination:=Range("A1:AL1"), _
Type:=xlFillDefault
Range("AR1") = "Test10"
Range("AR2") = ">0"

Columns("J:J").Select
Range("J1:J500").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("AR1:AR2"), Unique:=False


Regards
Claus Busch
 
Hi Eddie,

Am Mon, 20 Jun 2011 07:50:29 -0700 (PDT) schrieb webels:


you want find all positive values?
Try this:
Rows("1:1").Insert Shift:=xlDown
    Range("A1") = "Test1"
    Range("A1").AutoFill Destination:=Range("A1:AL1"), _
        Type:=xlFillDefault
    Range("AR1") = "Test10"
    Range("AR2") = ">0"

    Columns("J:J").Select
    Range("J1:J500").AdvancedFilter Action:=xlFilterInPlace, _
    CriteriaRange:=Range("AR1:AR2"), Unique:=False

Regards
Claus Busch
Click to expand...

HI Claus
Thanks for your suggestion, I wish however to find all values except
the word neg and the number 0 so there could be values such as <10.
Basically all records which do not contain 0 or neg in column J.
Thanks
Eddie
 
Hi Eddie,

Am Tue, 21 Jun 2011 13:15:38 -0700 (PDT) schrieb webels:
I wish however to find all values except
the word neg and the number 0 so there could be values such as <10.
Basically all records which do not contain 0 or neg in column J.
Click to expand...

then try this:
Rows("1:1").Insert Shift:=xlDown
Range("A1") = "Test1"
Range("A1").AutoFill Destination:=Range("A1:AL1"), _
Type:=xlFillDefault
Range("AR1") = "Test10"
Range("AR2") = "<>0"
Range("AS1") = "Test10"
Range("AS2") = "<>neg"

Columns("J:J").Select
Range("J1:J500").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("AR1:AS2"), Unique:=False


Regards
Claus Busch
 
Hi Eddie,

Am Tue, 21 Jun 2011 13:15:38 -0700 (PDT) schrieb webels:


then try this:
Rows("1:1").Insert Shift:=xlDown
    Range("A1") = "Test1"
    Range("A1").AutoFill Destination:=Range("A1:AL1"), _
        Type:=xlFillDefault
    Range("AR1") = "Test10"
    Range("AR2") = "<>0"
    Range("AS1") = "Test10"
    Range("AS2") = "<>neg"

    Columns("J:J").Select
    Range("J1:J500").AdvancedFilter Action:=xlFilterInPlace, _
    CriteriaRange:=Range("AR1:AS2"), Unique:=False

Regards
Claus Busch
Click to expand...

Thanks Claus
This removes the 0 from column J but not the word neg.
Thanks for your help with this, its much appreciated.

Eddie
 
Back
Top