Filtering with criteria in an remote cell

[Guest]

I have a list of records in col B thru F.
I wish to filter based on a start date and end date. These dates are located
in cells O3 and O5. The dates are in col B

Range("B1:F1").Select ' This is the header
Selection.AutoFilter Field:=1, Criteria1:=">==RANGE(""O3"")", Operator:= _
xlAnd, Criteria2:="<==RANGE(""O5"")

This filters out all records

Can anyone help?

oldjay

 

[merjet]

Here is a work-around.

Sub MyFilter()
Dim c As Range
Dim ws As Worksheet
UndoMyFilter
Set ws = Sheets("Sheet1")
'change range to suit
For Each c In ws.Range("B2:B12")
If c >= ws.Range("O3") And c <= ws.Range("O5") _
Then c.EntireRow.Hidden = True
Next c
End Sub

Sub UndoMyFilter()
'change rows to suit
Sheets("Sheet1").Rows("2:12").Hidden = False
End Sub

Hth,
Merjet

 

[Guest]

Thanks
I guess I didn't tell you enough. The list is variable and is defined as
"Database"
I also want to filter it on a variable in O7 . If the record is within the
date range and it matches O7 then show the row

I tried this but it didn't work

If c >= ws.Range("O3") And c <= ws.Range("O5")and c <>= ws.Range("O7") _

oldjay

 

[merjet]

"<>=" won't work if that is what you tried.

You could use:
For Each c In ws.Range("Database")
if it only refers to the one column, but I suspect not.

Sub MyFilter()
Dim c As Range
Dim ws As Worksheet
Dim iEnd As Long

UndoMyFilter
Set ws = Sheets("Sheet1")
iEnd = ws.Range("B2").End(xlDown).Row
For Each c In ws.Range("B2:B" & iEnd)
If c >= ws.Range("O3") And c <= ws.Range("O5") And _
c <> ws.Range("O7") Then c.EntireRow.Hidden = True
Next c
End Sub

Sub UndoMyFilter()
Dim iEnd As Long

iEnd = Sheets("Sheet1").Range("B2").End(xlDown).Row
Sheets("Sheet1").Rows("2:" & iEnd).Hidden = False
End Sub

Hth,
Merjet

 

[Guest]

That's what I tried "<>=" and as you said it doesn't work
The database is col b to col f

 

[Guest]

This code hides all the dates OK but doesn't hide the rows that do not match
O7 (which refers to col D)

c <> ws.Range("O7") Then c.EntireRow.Hidden = True
I hope you understand what I mean
oldjay

 

[merjet]

The variable c is for column B. If you want to compare column D to
cell O7, then use c.offset(0,2) instead.

Hth,
Merjet

 

[Guest]

Sorry to be such a dummy but this doesn't filter anything

oldjay

Sub MyFilter()
Dim c As Range
Dim ws As Worksheet
Dim iEnd As Long

UndoMyFilter
Set ws = Sheets("Summary")
iEnd = ws.Range("B2").End(xlDown).Row
For Each c In ws.Range("B2:B" & iEnd)
If c >= ws.Range("O3") And c <= ws.Range("O5") And _
c.Offset(0, 2) <> ws.Range("O7") Then c.EntireRow.Hidden = True
Next c

End Sub

 

[merjet]

I am sending a file to your e-mail address
to show that it does filter some rows.

Merjet

 

[Guest]

I am sending a file to your e-mail address
to show that it does filter some rows.

Merjet


Thanks

Please send it to jauld1@hotmail. com. I can't get an attachment from an
unknown source

 

[Guest]

Your file works OK with date in con d but not with names

Try this
Date Employee
1/1/2007 TERESA SHAFFER
1/11/2007 PETE QUEEN
1/21/2007 LOIS SMITH
1/31/2007 LORI SEWELL
2/10/2007 HAILE SELASSIE
2/20/2007 WILMA JAMES
3/2/2007 MARTY YOUNG
3/12/2007 ELIZABETH BRIGHT
3/22/2007 SALLY MCCAFFERTY
4/1/2007 ELIZABETH BRIGHT
4/11/2007 DALE BOLAND

oldjay

 

[Guest]

This is the correct code. Had to change the last And to Or

If c >= ws.Range("O3") And c <= ws.Range("O5") Or _
c.Offset(0, 2) <> ws.Range("O7") Then c.EntireRow.Hidden = True

 

[Guest]

Wrong Wrong - This just filters for d col

This is the correct code. Had to change the last And to Or

If c >= ws.Range("O3") And c <= ws.Range("O5") Or _
c.Offset(0, 2) <> ws.Range("O7") Then c.EntireRow.Hidden = True