Macro to select uk format date range within sheet of date

[edmundmckay]

Hi

I currently have the below macro which I am trying to achieve. The aim is using a control set of two dates as a range on a tab called "Macro Button" and applying them to select a range of dates in column C on a sheet called "Rent Arrears".

The below macro works in the scenario if I just wanted to filter on two specific ranges but the actually custom filter is to filter first anything prior to Daterange1 (C3) on Macro button tab and then filter anything after Daterange2 (C4) on Macro Button tab.

I have tried to insert "<" & in front of criteria1 and 2 in the code but all this does is filter column C with no results.

Please could some point me with the right amendment to achieve the filter range desired.

Sub dateboxfiltermacro()


Dim Lastrow As Long
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Dim Daterange1 As String
Dim Daterange2 As String
Daterange1 = Sheets("Macro Button").Range("C3").Value
Daterange2 = Sheets("Macro Button").Range("C4").Value
ActiveSheet.Range("A5:N5" & Lastrow).AutoFilter Field:=3, Criteria1:=Daterange1, Operator:=xlOr, Criteria2:=Daterange2

End Sub

many thanks in advance

 

[James006]

Hi,

Most probably you are working with actual dates ... or more precisely numbers ...

As a consequence instead of

[COLOR=#333333][I]Dim Daterange1 As String[/I][/COLOR]
[COLOR=#333333][I]Dim Daterange2 As String[/I][/COLOR]

you should test

[COLOR=#333333][I]Dim Daterange1 As Value[/I][/COLOR]
[COLOR=#333333][I]Dim Daterange2 As Value[/I][/COLOR]

Hope this will help

 

[Joe4]

you should test

[COLOR=#333333][COLOR=#333333][I]Dim Daterange1 As Value[/I][/COLOR]
[COLOR=#333333][I]Dim Daterange2 As Value[/I][/COLOR][/COLOR]

"Value" is not a valid data type, and will return compile errors.

 

[James006]

Oops ... I meant Date ... and Not Value ...

 

[edmundmckay]

Hi James and Joe.

Thanks for getting involved so quickly with the query. So I have changed the variant to Date as below but sadly still doing the same as before. I have inserted a screen shot of what result is happening on the data. If you actually manually go into the custom filter, you can read that the macro filter instructions have been met but for some reason those items in column C aren't being selected.
Any thoughts on this?

Dim Daterange1 As Date
Dim Daterange2 As Date

cheers