VBA filtering a list between 2 dates from cell references - help please.

[Jed Shields]

Hi guys,

I need a little help with some code I'm writing. I'm trying to filter a list between two dates that are selected using drop down lists in Cells CZ1 and CZ2.

The first part of the code works ok ie it filters the list by column 28 as "Shipped" only.

The second part is causing me trouble.

The third part sorts the list by date order based on column 48 and this also works ok.

[FONT=Arial][SIZE=2]Sub FilterTransactionDates()[/SIZE][/FONT]
  
 [FONT=Arial][SIZE=2]    With  Sheet1
        .AutoFilterMode =  False
         .Range("A1:CU1").AutoFilter
         .Range("A1:CU1").AutoFilter Field:=28,  Criteria1:="Shipped"
         .Range("A1:CU1").AutoFilter Field:=48, Criteria1:>=Range("CZ1").Value,  Operator:= _
        xlAnd,  Criteria2:<range("cz2").value

       '  Selection.Sort Key1:=Range("CT3"), Order1:=xlAscending, Header:=xlGuess,  _
       ' OrderCustom:=1, MatchCase:=False,  Orientation:=xlTopToBottom
    End  With
        
End  Sub</range("cz2").value
[/SIZE][/FONT]

It's not very pretty as I've been copying from various posts online.

Cheers,
Jed.

 

[Jed Shields]

Hmmm ... first post seems bugged as it's not showing all of the code (at least not on my PC). Here it is again:

[FONT=Arial][SIZE=2]Sub FilterTransactionDates()[/SIZE][/FONT]
  
 [FONT=Arial][SIZE=2]    With  Sheet1
        .AutoFilterMode =  False
         .Range("A1:CU1").AutoFilter
         .Range("A1:CU1").AutoFilter Field:=28,  Criteria1:="Shipped"

         .Range("A1:CU1").AutoFilter Field:=48, Criteria1:>=Range("CZ1").Value,  Operator:= _
        xlAnd,  Criteria2:

       '  Selection.Sort Key1:=Range("CT3"), Order1:=xlAscending, Header:=xlGuess,  _
       ' OrderCustom:=1, MatchCase:=False,  Orientation:=xlTopToBottom
    End  With
        
End  Sub
[/SIZE][/FONT]

 

[Trevor G]

You might have to show the cell values as long as excel isn't keen on filtering in vba by dates. Sample below:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><SPAN style="color:#007F00">'Code to apply a filter for dates before tomorrow</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Criteria <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>Criteria = <SPAN style="color:#00007F">Date</SPAN> + 1<br> <br>    ActiveSheet.Range("$C$1:$C$22").AutoFilter Field:=1, Criteria1:="<" & Criteria, Operator:=xlAnd<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Jed Shields]

I've tried the following and the second part is still failing, it's giving me an "expected named parameter" message:

[FONT=Arial][SIZE=2]Sub FilterTransactionDates()[/SIZE][/FONT]
  
[FONT=Arial][SIZE=2]Dim Date1 As Long
Dim Date2 As Long[/SIZE][/FONT]
  
[FONT=Arial][SIZE=2]Date1 = Range("CZ1")
Date2 =  Range("CZ2")[/SIZE][/FONT]
  
 [FONT=Arial][SIZE=2]    With  Sheet1
        .AutoFilterMode =  False
         .Range("A1:CU1").AutoFilter
         .Range("A1:CU1").AutoFilter Field:=28,  Criteria1:="Shipped"

      [COLOR=Red]   .Range("A1:CU1").AutoFilter Field:=48, Criteria1:>=Date1, Operator:=  _
        xlAnd,  Criteria2:<date2< font="">

        [COLOR=black]Selection.Sort  Key1:=Range("CT3"), Order1:=xlAscending, Header:=xlGuess,  _
        OrderCustom:=1,  MatchCase:=False, Orientation:=xlTopToBottom
    End  With
        
End  Sub[/COLOR]</date2<>[/COLOR][/SIZE]
[/FONT]

 

[Trevor G]

You aren't showing what Criteria 2 is so it fails.

Have tried this, but not tested it fully.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> FilterTransactionDates()<br><SPAN style="color:#00007F">Dim</SPAN> Date1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Date2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <br>Date1 = Range("CZ1")<br>Date2 = Range("CZ2")<br>Sheets("Sheet2").Range("a1").Select<br>Range("A1:CU1").AutoFilter<br>Range("A1:CU1").AutoFilter Field:=28, Criteria1:="Shipped"<br>Range("$A$1:$AV$35").AutoFilter Field:=48, Criteria1:=Date1, Operator:=xlAnd, Criteria2:="<=" & Date2<br><SPAN style="color:#007F00">'Change the range data</SPAN><br><SPAN style="color:#007F00">'Not tested the sorting but rest works as a filter</SPAN><br><br><br><SPAN style="color:#007F00">'        Selection.Sort Key1:=Range("CT3"), Order1:=xlAscending, Header:=xlGuess, _<br>'        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom</SPAN><br>    <br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Jed Shields]

Thanks Trevor, I'll give it a go.

There should have been code after Criteria2 - I seem to be having trouble pasting into the forum at the moment - very odd.