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.

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Jun 20, 4:19*pm, Claus Busch wrote:
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.

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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.

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Jun 22, 6:55*am, Claus Busch wrote:
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.

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

Eddie

Hi Eddie,

Am Thu, 23 Jun 2011 02:53:54 -0700 (PDT) schrieb webels:

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

how do you write "neg"? if you write "neg." then change code to:

Range("AR1") = "Test10"
Range("AR2") = "<0"
Range("AS1") = "Test10"
Range("AS2") = "<neg."


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2