472,143 Members | 1,421 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,143 software developers and data experts.

How to replace a criteria in AutoFilter by a variable?


I have this code:

Sub Macro4()
' Macro4 Macro

ActiveSheet.Range("$A$1:$E$25").AutoFilter Field:=1, Criteria1:="16/08/2008", _
End Sub

Instead of having to insert "16/08/2008" as Criteria1, I would like to have a variable in there corresponding to the maximum value of a column containing dates.

Could anyone help? That would be extremely helpful and very much appreciated...
Jul 29 '10 #1
9 8975
In this case the maximum would correspond to the most recent date in the DATE column....

01.01.2008 John SHOES 10 100
02.01.2008 John TROWSERS 20 110
03.01.2008 John SHIRTS 10 80
04.01.2008 John JEANS 25 90
05.01.2008 John SOCKS 20 95
06.01.2008 John SHOES 32 75
07.01.2008 John TROWSERS 45 65
08.01.2008 John SHIRTS 11 95
09.01.2008 John JEANS 12 12
10.01.2008 John SOCKS 14 13
11.01.2008 John SOCKS 10 100
12.01.2008 John SHOES 20 110
12.01.2008 John TROWSERS 10 80
12.01.2008 John SHIRTS 25 90
13.01.2008 John JEANS 20 95
14.01.2008 John SOCKS 32 75
15.01.2008 John SOCKS 45 65
16.01.2008 John SHOES 11 95
16.01.2008 John TROWSERS 12 12
16.01.2008 John SHIRTS 14 13
Jul 29 '10 #2
Guido Geurs
767 Expert 512MB
I hope this will help (see attachment):

Expand|Select|Wrap|Line Numbers
  1. Sub Macro4()
  2. Dim DateSelect As Date
  3.    DateSelect = Selection
  4.    Range("A1").Select
  5.    Selection.AutoFilter
  6.    ActiveSheet.Range("$A$1:$E$25").AutoFilter Field:=1, Criteria1:=Format(DateSelect, "dd.mm.yyyy"), _
  7.    Operator:=xlAnd
  8. End Sub
Jul 29 '10 #3
Thank you so much for your reply.

It says type mismatch regarding the DateSelect...
Jul 29 '10 #4
Guido Geurs
767 Expert 512MB
It's working on my PC.
I'm working with Excel 2003 ! You to ?
You must select a cell with a date like "G1" or "H1" !!!!
If You select a text cell than You will have an error.
Can You exclude the selection of a non date cell ?
I will try to find an solution.
If You can't solve it, is it possible to attach Your sheet ?
Jul 29 '10 #5
Guido Geurs
767 Expert 512MB
I think this will do it=

Expand|Select|Wrap|Line Numbers
  1. Sub Macro4()
  2. Dim DateSelect As Date
  3.    On Error GoTo Bad_Selection
  4.    DateSelect = Selection
  5.    Range("A1").Select
  6.    Selection.AutoFilter
  7.    ActiveSheet.Range("$A$1:$E$25").AutoFilter Field:=1, Criteria1:=Format(DateSelect, "dd.mm.yyyy"), _
  8.    Operator:=xlAnd
  9. Exit Sub
  10. Bad_Selection:
  11.    MsgBox "Select a date !"
  12. End Sub
Jul 29 '10 #6
Thank you very much for your reply

I think we are almost there... but I do have a problem with the date selection. In the file you attached ("How to....xls"), the dates are formatted as Text, therefore the filter works (Except for cell G1 which is formatted as Date, but the macro keeps functioning anyways).

My dates are formatted as dates and for some reason the macro doesn't recognise them as dates. So I tried to format them as Text as you did, but then values like 40380.... appear.

You will see a proof in the attachments: I copied identically your macro but because of my formatting of the date it won't work as it should.

Do you know what I can do about this?

Thank you so much.
Attached Files
File Type: zip Book11.zip (13.3 KB, 105 views)
Jul 29 '10 #7
Guido Geurs
767 Expert 512MB
After searching the web I have found out that autofiltering on dates isn't so easy.
What I have found is that autofilter uses the US format to filter on dates.
So I have changed the sheet dates (are = date AND time) to "mm/dd/jjjj" format and formated the var "DateSelect" to "mm/dd/yyyy".
And surprisingly it's working ! (see attachment)

Expand|Select|Wrap|Line Numbers
  1. Sub Macro5()
  2. Dim DateSelect As String
  3.    On Error GoTo Bad_Selection
  4.    If IsDate(Selection) Then
  5.       DateSelect = Format(Selection, "mm/dd/yyyy")
  6.       Range("A2").AutoFilter Field:=1, Criteria1:=DateSelect, Operator:=xlAnd
  7.    Else
  8.       GoTo Bad_Selection
  9.    End If
  10. Exit Sub
  11. Bad_Selection:
  12.    MsgBox "Select a date !"
  13. End Sub
Attached Files
File Type: zip Book11_v3.zip (14.9 KB, 90 views)
Jul 30 '10 #8
Guido Geurs
767 Expert 512MB
If the user want to see the date in an other format (not "mm/dd/yyyy") than You can let the 1e col in the users format and hide a col with the reference to col A but with the US format and filter on that col (col "F" in the attachment).
Attached Files
File Type: zip Book11_v4.zip (16.4 KB, 143 views)
Jul 30 '10 #9
Thank you sooo much for your help, it is immesnily appreciated
Jul 30 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Ones Self | last post: by
16 posts views Thread by BBM | last post: by
7 posts views Thread by gabriel.becedillas | last post: by
10 posts views Thread by n.torrey.pines | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.