Filter not working when criteria is equal to time???? RRS feed

  • Question

  • Hi All,

    I try to filter column 2 in sheet 3 based on equal to time (that is hh:mm:ss.00 format) is in the sheet 4 in column M

    m values will be, say, 2, 3, 4 etc. 

    Why not following statement does not take the right logic?

    Sheets(3).Range("A1:J20").AutoFilter Field:=2, Criteria1:=Sheets(4).Range("M" & m).Value  


     Sheets(3).Range("A1:J20").AutoFilter Field:=2, Criteria1:="=" & Sheets(4).Range("M" & m).Value

    Thanks in advance. 

    Thursday, July 12, 2018 8:54 PM

All replies

  • Try:

    Sheets(3).Range("A1:J20").AutoFilter Field:=2, Criteria1:=Format(Sheets(4).Range("M" & m).Value, "hh:mm:ss.00")

    Regards, Hans Vogelaar (

    Thursday, July 12, 2018 10:27 PM
  • Oops - VBA does not recognize fractions of seconds. Does it work if you use

    Sheets(3).Range("A1:J20").AutoFilter Field:=2, Criteria1:=Sheets(4).Range("M" & m).Text

    Regards, Hans Vogelaar (

    Friday, July 13, 2018 2:05 PM
  • Hi Hans Vogelaar, 

    It works!! Thanks a lot for your idea.



    Friday, July 13, 2018 2:09 PM