Filter a list by using advanced criteria

Is it my imagination or is ADVANCED FILTER just plain flakey?

Bill
I also often have problems with using the filter and would be very interested to find out how to do what you want.

In the meantime, the alternatives below might help (although they’re a bit messy).

The following macro will hide rows other than those for Feb (2) and Mar (3). You can change the code to fit the months and number of months as required. The range of cells containing the dates has to be selected before running the macro.

Sub FilterMonths()
Dim cell As Range
For Each cell In Selection
If Month(cell) <> 2 And Month(cell) <> 3 Then
cell.EntireRow.Hidden = True
End If
Next cell
End Sub

Another way is as follows :-

Let’s say your dates are in column A and start in cell A1. Also, you want to hide all rows except for Feb and Mar.

-In an empty column put this formula in the first cell and drag it down as far as necessary :-
=IF(MONTH(A1)=2,A1,IF(MONTH(A1)=3,A1,""))

-Select the column that contains the formulas

-Select Edit / Go To… / Special… / Formulas -Text
(This should highlight the blank formula cells)

-Select Format / Row / Hide
(This should hide the blank formula rows)

Celia

Celia,

I do use formulas to control data in the seemingly the same way as you.

I instead AUTOFILTER the outcome to hide those rows not needed.
--------------------------
The code is interesting.

I see what I can accomplish with it.
--------------------------
I can get ADVANCED CRITERIA to work.

It seems that CRITERIA range (in rows) must equal the amount of CRITERIA you use.

I was specifying 3-4 rows of CRITERIA when in fact, was only supplying 1-2 rows.

ADVANCED CRITERIA was expecting more and so, bombed out.

I pulled this list from MSACCESS.

One might wonder why I'd prefer the power of EXCEL over ACCESS.

Easy. Just doing "DIRTY" work or just don't know MSACCESS well enough to acomplish my task.

Well, that's not true cuz I can do it: just much easier in EXCEL!
--------------------------
I GOT IT TO WORK!!!!

Assuming data starts at RANGE("A1")

1) Make sure your data has column headings.

2) Insert 4-5 rows above column headings.

3) Copy original column heading to first row.

4) Specify criteria under 2nd column headings.
(i.e. >01/31/1999 in row B to specify all records greater than 31-DEC-1999.

5) Click on an item with list (data below original headings).

6) Select ADVANCE FILTER under DATA|FILTER menu.

7) List should be highlighted. If not, select list WITH column headings.

8) CRITERIA range should be 2nd headings (ROW 1) and all rows with CRITERIA you specify.

9) BOOM! You're there!
--------------------------
I still had a problem specifying a range.

Perhaps I just don’t know the semantics well enough.

EXCEL HELP is not to informative.

I DID manage to get it to work though!!

The trick I used was to insert a copy of the date column beside itself.

Then, I could specify records > than a date in the first column and I could specify records < a date in the second column!

This CRITERIA remained in the first row of CRITERIA data. Of course in different cells!

Bill

Celia,

To REALLY make the ADVANCED filter Hum, Do this!

Copy the AskMrExcel code from 12/23/98 and modify.

My previous example uses one row as criteria but instead of the two cell entries i stated before, I use three.

So now I filter a particular group of records within a given date range!

The information that changes is in:
{ (2,8) (2,10) (2,11) }

So my domain is 2 and my range is (8,10,11)

The TARGET.COLUMN (not well documented by MrExcel) refers to column that was changed.

Ditto for TARGET.ROW.

----------------------------------------

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 8 Or Target.Column = 10 Or Target.Column = 11 Then
If Target.Row = 2 Then
Range("A6:O195").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Rows("1:2"), Unique:=False
Range("H2").Select
End If
End If
End Sub
------------------------------------
Bingo!!!!!!!!!

Heck, now I'm building the date from scratch and having the EVENT MACRO paste it into the CRITERIA!

Oh Joy!