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

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

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" wrote:

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

"<=" 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

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


"merjet" wrote:

"<=" 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

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" wrote:

"<=" 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

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