Need to commit operand in autofilter

M

metropol

Board Regular
Joined
Aug 19, 2005
Messages
209
I have a value 'cDate' in a sheet called Values. It stores today date.

A macro is doing a autofilter on date in column 8 based on cDate:

Code: 
sub autofilter()

cperiod = Sheets("Values").Range("cDate")

Selection.Autofilter Field:=8, Criteria1:=">" & cperiod, Operator:=xlAnd _
   , Criteria2:="<" & cperiod + 7

End sub

When I run this macro, the autofilter returns nothing. All rows are hidden. BUT when i open the autofilter selection and then press enter, correct rows are displayed.

Is there another workaround for doing this commitment in the macro??
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Hi metropol,

It's a really good habit to declare your variables. You can enforce variable declaration by adding an Option Explicit statement to the top of your code module. You can have this done for you automatically in new code modules by, in the VBE, going to Tools | Options and ticking the Require Variable Declaration checkbox. ;)

Also, it's best not to call your subroutine "Autofilter" because that's already the name of an object in Excel's object model and it's also a property and/or method of several objects. This can lead to confusion - in fact, if this code were in a sheet class module rather than a standard code module, it wouldn't even compile.


In this case, I think the situation will be fixed by declaring your cperiod variable as a Long data type:
Rich (BB code): 
Dim cperiod As Long

cperiod = Sheets("Values").Range("cDate").Value

Selection.Autofilter Field:=8, Criteria1:=">" & cperiod, Operator:=xlAnd _
   , Criteria2:="<" & cperiod + 7

End sub


You might also want to consider directly using the range object rather than the selection object?


Hope that helps...
 
Upvote 0
You must log in or register to reply here.

Similar threads

airforceone
  • Solved
AutoFilter Working but not on Date
Replies
5
Views
631
Alex Blakenburg
A
T
  • Solved
AutoFilter Multiple Criteria
Replies
6
Views
899
Peter_SSs
Peter_SSs
V
  • Solved
AutoFilter now working
Replies
5
Views
386
Joe4
Joe4
N
  • Solved
Referencing code to a specific cell in Sheet1
Replies
3
Views
223
Joe4
Joe4
M
  • Solved
Is there a VBA to select only visible rows numbered two (2) or higher?
Replies
7
Views
792
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,991
Messages
6,122,628
Members
449,095
Latest member
bsb1122

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top