cell ref in filter macro 2

[JesOakley]

Hi everyone.
I have a monthly task which requires i apply a complex series of custom filters to a spreadsheet and extract the data. I thought it would be cool to automate the filters to ensure consistency. Most of the filters include date-filtering i.e. in the current month, before the current month, etc.
First i recorded a macro applying some of the filters. Looking at the code I can see the dates hard-coded (as I entered them) e.g.

Selection.AutoFilter Field:=3, Criteria1:=">=11/01/2009", Operator:=xlAnd _
        , Criteria2:="<=11/30/2009"

but it would be best for me to automate these dates. So I created cells with formulas that return the dates I will need. However, I am unsure how to replace the hardcoding with the cell ref so that it comes out correctly formatted etc.
Can anyone help please?

 

[PHV]

Replace this:
Criteria1:=">=11/01/2009"
with something like this:
Criteria1:=">=" & yourCellReference.Text

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JesOakley]

Thanks for that PHV, however you have made the mistake of assuming that I'm not a numpty, because

Criteria1:=">=" &Rules!A24

gave me error 424. Anyway I looked at the help and changed it so it's now like this:

    Set startDate = Worksheets("Rules").Range("A24")
    Set endDate = Worksheets("Rules").Range("B24")

    Selection.AutoFilter Field:=3, Criteria1:=">=" & startDatec.Text, Operator:=xlAnd _
        , Criteria2:="<=" & endDate.Text

The only problem now is that when i go into the custom filter to see what it has done, i find it has set the start date to >= 11/01/2009 and the end date to <= 30/11/2009, despite the fact that both dates show as dd/mm/yyyy in their calculated cells. I can see that because the start date is valid as mm/dd/yyyy it has formatted it that way. Because the end date is invalid that way, it has made it dd/mm/yyyy. Any ideas on how to format these consistently so that the filter works? When I type them in manually, I find it only works with both dates set to mm/dd/yyyy.
Also, since i have a number of macros using the same start and end dates, is there anywhere 'higher up' that i can set them so i don't have to repeat the code in each subroutine?

 

[PHV]

Replace this:
Criteria1:=">=" & startDatec.Text
with this:
Criteria1:=">=" & Format(startDatec.Value, "mm/dd/yyyy")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AnotherHiggins]

So I created cells with formulas that return the dates I will need.

Just use that same logic to return the dates in the macro itself and ditch the helper cells.

Example - I might use the following for your above scenario:

Dim dteEOM as date
'....

    Dim dteBegLstMonth  As Date
    Dim dteEndLstMonth As Date
    '....
    dteBegLstMonth = Int(DateAdd("m", -1, Now()) - Day(DateAdd("m", -1, Now()))) + 1
    dteEndLstMonth = Int((Now() - Day(Now())))

    Cells.AutoFilter Field:=3, Criteria1:=">=" & dteBegLstMonth, Operator:=xlAnd, _
            Criteria2:="<=" & dteEndLstMonth

Now some tips:
[tab]1) Notice that I didn't use Selection. You should avoid using Select and Activate whenever possible. They only slow things down.

[tab]2) Consider whether you really want to distribute the data with the Filters in place. Often it's better to just give the data that's needed without lots of unneeded data hidden behind filters where a user can easily unhide it. To accomplish this, filter 'the other way' - that is, filter for the stuff you DO NOT want, then delete the resulting rows. At then end when you remove the filter, you're left with ONLY the data that is needed.

To use the above example, we'd change it to look like this:

    Dim dteBegLstMonth                            As Date
    Dim dteEndLstMonth                            As Date
    '....
    dteBegLstMonth = Int(DateAdd("m", -1, Now()) - Day(DateAdd("m", -1, Now()))) + 1
    dteEndLstMonth = Int((Now() - Day(Now())))

    Cells.AutoFilter Field:=3, Criteria1:="<=" & dteBegLstMonth, Operator:=xlOr, _
            Criteria2:=">=" & dteEndLstMonth
    
    Range("C2:C65536").EntireRow.Delete

    Sheets("Sheet1").AutoFilterMode = off

[tab] 3) Do the dates in your file contain time information? That is, if you click on one of those cells do you see the time in the formula bar, like "12/1/2009 7:35:00 PM"? If so, then you need to change the way you're filtering because "11/30/2009 12:00:01 AM" is greater than "11/30/2009".

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[JesOakley]

Wow. Lots of good info there. Thanks a lot anotherhiggins and PHV both. I ditched the helper cells as suggested, and the format command works a treat too, so kudos to both.