Magician required for multiple Criteria Extraction

[mikemcbain]

I have a very large database in Excel 2016 and for the life of me I cannot get it to do what I used to be able to do in Excel 2003.

I have it set up with the data Input under headings from A7 to R7
My Criteria headings are A1 to R1 and my Output headings are from V1 to AM1

I am using a Macro to do the extraction...

Sub Extract()
'
' Extract Macro
'
' Keyboard Shortcut: Ctrl+e
'
        rw_s = Application.WorksheetFunction.CountA(Range("U:U"))
   
    Range("A7:R" & rw_s).Select  
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Range("A7:R" & rw_s).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "A1:R3"), CopyToRange:=Range("V1:AM1"), Unique:=False        
       Range("AA2").Select
 End Sub

Now my problem is that I want to extract Criteria in one column that is within the range >97 and <179 so I have entered it thus...
RTG
>97
<179

and I am using the Criteria range A1:R3 see above but it is not working.

If I just put the Criteria in my macro as A1:R2 it extracts everything >97 perfectly.

What am I doing wrong or is this later version of Excel unable to do that?

With high expectations

Mike
Tasmania.

 

[Peter_SSs]

Now my problem is that I want to extract Criteria in one column that is within the range >97 and <179 so I have entered it thus...
RTG
>97
<179

and I am using the Criteria range A1:R3 see above but it is not working.

With Advanced Filter, putting multiple conditions in a column like that means you are filtering for >97 OR <179, which of course will be true for all values.

For AND conditions, you need multiple columns for that field and so you may require more columns for your criteria conditions, but the criteria range will still be from row 1 to row 2. So criteria range may, for example be
"A1:S2"

Excel Workbook
	I	J
1	RTG	RTG
2	>97
Adv Filter

See here for more on Adv Filter, including AND v OR

 

[mikemcbain]

Thank you very much Peter, adding that additional column required for multiple Criteria solved my problem nicely.

Happy Easter

Mike.

 

[Peter_SSs]

Thank you very much Peter, adding that additional column required for multiple Criteria solved my problem nicely.

Happy Easter

Mike.

You are welcome. Thanks for letting us know.