Filtering dates

[austenr]

I'm trying to filter between dates on the WB attached and the code below and I'm stuck. I think my ranges are off either in the subs or in the Name Manager or both. I know the code only deals with one date and not two. Not sure how to modify it to two dates.

Sub ApplyFilter()Dim wsDL As Worksheet
Dim wsO As Worksheet
Dim rngAD As Range
Set wsDL = Sheets("DateList")
Set wsO = Sheets("Time")
Set rngAD = wsO.Range("AllDates")
'update the list of dates
  wsDL.Range("A1").CurrentRegion.ClearContents
  'rngAD.Offset(-1, 0).Resize(rngAD.Rows.Count + 1).Select
  rngAD.AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:="", _
    CopyToRange:=wsDL.Range("A1"), Unique:=True
  wsDL.Range("A1").CurrentRegion.Sort _
      Key1:=wsDL.Range("A2"), Order1:=xlAscending, Header:=xlYes
'filter the list
wsO.Range("Database").AdvancedFilter _
    Action:=xlFilterInPlace, _
    CriteriaRange:=wsO.Range("A3:B3"), Unique:=False
End Sub


Sub RemoveFilter()
On Error Resume Next
    ActiveSheet.ShowAllData
End Sub

[p45cal]

In the attached is a button at cell F1 of the Time sheet which runs a macro containing the single line:
Range("A6:F26").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("H1:H2"), Unique:=False
All hard-coded, for you to adapt.
Note that:
Advanced filter wants to see a range for the CriteriaRange, not textboxes
The filtered range must include the headers
The CriteriaRange must include a row at the top for headers (in this case a blank cell as I've used a formula(cell H2))

[austenr]

Thanks so much. Two Questions. I need to have this work on a range other than a fixed range and secondly a way to release the filter . Thanks again.

[p45cal]

I need to have this work on a range other than a fixed range

We'll need info on how the range can vary; will it always start in a given row? Will there be a constant number of columns? Will it always be on a given sheet? etc. etc.

and secondly a way to release the filter.

You already have that in your RemoveFilter() macro.

[austenr]

We'll need info on how the range can vary; will it always start in a given row? Will there be a constant number of columns? Will it always be on a given sheet? etc. etc.





You already have that in your RemoveFilter() macro.

I’m worked on it and almost have it where I want it except for one thing. When the Weekly Report button gets ran the Outlook emails that get produced have all the rows for each person but what I need to do is have them show just the filtered rows by date

I suspect that the function or sub that is called when the Weekly Report button is clicked undoes the filter done by date.

If if I can get this to do the outlook email part with the date filter to act correctly I’ll be all set.

Many thants to yoy you and Paul and Ken Hobbs for helping.

[p45cal]

I suspect that the function or sub that is called when the Weekly Report button is clicked undoes the filter done by date.

If you attach an updated file to show your current code we can probably help.

[austenr]

here ya go

[p45cal]

You were trying to add an Autofilter on top of an Advanced filter and it was the Autofilter which was unhiding previously hidden rows (by Advanced filter).
Try the attached, comments in the code.
Whether you use the Filter by dates only button is up to you - it doesn't change the output of Send Weekly Report button.

[austenr]

Thanks that works wonderfully. Much appreciated.