Solved

Excel VBA custom autofilter not finding records

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now