Solved

Excel VBA custom autofilter not finding records

Posted on 2011-02-11
2
283 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 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