• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

Excel - Macro

I am trying to use selection.autofilter in my sub procedure. I have declared two variables newdate and newdate1 as variants and formatted them as "dd/mm/yyyy" My problem comes when I try to use them in the selection.autofilter the first variable is inserted in criteria1 which produces the correct result "dd/mm/yyyy" however, the second variable which I put into criteria2 comes up with the DateValue instead of the actual typed in date. Why is criteria2 not appearing in the format "dd/mm/yyyy" when they are both declared and processed in the same fashion?

  • 2
  • 2
1 Solution

If I'm not wrong, VBA assumes that you use mm/dd/yyyy (not dd/mm/yyyy). That is the cause of your problems. To avoid misunderstandings, you can use YYYY-MM-DD format or even better DateSerial(y,m,d), as shown in this example:

Sub AutoFilterTest()
  'Declare variables
  Dim ws As Worksheet
  Dim d1 As Variant
  Dim d2 As Variant

  'Set values of variables
  Set ws = ActiveWorkbook.Sheets("Sheet2")
  d1 = DateSerial(1999, 9, 1)
  d2 = DateSerial(1999, 12, 31)

  'Make sure that AutoFilter is turned off
  ws.AutoFilterMode = False

  'Use AutoFilter to display records from d1 to d2
  ws.Range("A1").AutoFilter _
    Field:=1, _
    Criteria1:=">=" & d1, _
    Operator:=xlAnd, _
    Criteria2:="<=" & d2
End Sub

Ture Magnusson
Karlstad, Sweden
NissargAuthor Commented:
Hi Ture

Thanks for your response. This client insists on having the dd/mm/yyyy format. When I run my macro on NT Server it works when the locale settings are dd/mm/yyyy. Is there no way to change this on Windows 98. I have tried the Regional Settings - doesn't help.


It is ok that your client wants dd/mm/yyyy date format. You'll just have to make sure that VBA interprets the date correctly.

If I am not mistaken, the CDATE function will convert a text string to a date, using the windows regional settings. Try this little prrocedure to check out the CDATE function and see if it reports the correct month.

Sub TestCDate()
  Dim t As String
  Dim d As Date
  t = "01/07/2000"
  d = CDate(t)
  MsgBox Month(d)
End Sub

NissargAuthor Commented:
Have not run it yet, but I can see what you are saying.

Thanks Ture

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now