Solved

Excel VBA custom autofilter not finding records

Posted on 2011-02-11
2
284 Views
Last Modified: 2012-05-11
I have a bit of a strange issue which I can't seem to solve.

I have a macro which drives an autofilter from 4 possible filter criteria. 3 of these are a direct population of a variable into the appropriate filter field and work fine, but 1 results in a custom filter and doesn't return any records.

When I look at the filtered sheet I can see that the filter field is active, and when I click on the filter arrow it shows that the custom criteria has been populated with the correct variables, yet the sheet shows 0 records. However, when I click 'OK' in the custom filter popup box, it suddenly works correctly and returns the records which I know are there. ???

I've included an extract of the code. Basically it is intended to check a cell to see if the user has selected a Month or Quarter from the drop down menu. On the sheet this drives a lookup to identify the start and end date for the month/quarter, and the code uses the results of the lookup to set the custom filter to include only those records which are between the start and end of the chosen period. If the user hasn't selected a filter for the period then it is completely ignored.

Thanks for your help Experts, I'm stumped on this one.

Let period_filter = Sheets("Reports").Range("d12").Value
If period_filter <> "" Then
    Let period_start = Sheets("Reports").Range("d13").Value
    Let period_end = Sheets("Reports").Range("e13").Value
    Selection.AutoFilter Field:=6, Criteria1:=">=" & period_start, Operator:=xlAnd _
    , Criteria2:="<=" & period_end
Else
End If

Open in new window

0
Comment
Question by:pendulum
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34872793
Try:
Let period_filter = Sheets("Reports").Range("d12").Value
If period_filter <> "" Then
    Let period_start = Sheets("Reports").Range("d13").Value
    Let period_end = Sheets("Reports").Range("e13").Value
    Selection.AutoFilter Field:=6, Criteria1:=">=" & clng(period_start), Operator:=xlAnd _
    , Criteria2:="<=" & clng(period_end)
Else
End If

Open in new window

0
 

Author Closing Comment

by:pendulum
ID: 34872850
Awesome, thanks for your help.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question