Solved

Excel Filter macro on two variables

Posted on 2014-02-12
2
408 Views
Last Modified: 2014-02-12
Sub Macro3()
'
' Macro3 Macro
'

'
    ActiveSheet.Range("$A$39:$DL$22265").AutoFilter Field:=52, Criteria1:= _
        ">=Para_Age_Min", Operator:=xlAnd, Criteria2:="<=Para_Age_Max"
End Sub


I have two variable
Para_Age_Min and and Para_Age_Max and am trying to write a macro which will sort a database ("$A$39:$DL$22265") by filtering only records that are >=Para_Age_Min and <=Para_Age_Max

Para_Age_Min and Para_Age_Max are numbers such as 2 and 6 and so I am trying to filter for example >=2 and <=6 for records in column 52

I am sure this problem would probably have been addressed in the past but my macro wont work as it return no records when it filters.

ie no records meet the criteria even though there are records that

I havent attached a file as it is simply too big but would appreciate if anybody can see if my rather simple macro has some form of logic or other problem

Thanks

Paul Collins
0
Comment
Question by:snapper1
2 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
Hi,

if they are variables

pls try

Sub Macro3()
'
' Macro3 Macro
'
    ActiveSheet.Range("$A$39:$DL$22265").AutoFilter Field:=52, Criteria1:= _
        ">=" & Para_Age_Min, Operator:=xlAnd, Criteria2:="<=" & Para_Age_Max
End Sub

Open in new window


if named ranges
[code]Sub Macro3()
'
' Macro3 Macro
'
    ActiveSheet.Range("$A$39:$DL$22265").AutoFilter Field:=52, Criteria1:= _
        ">=" & Range("Para_Age_Min"), Operator:=xlAnd, Criteria2:="<=" & Range("Para_Age_Max")
End Sub

Open in new window


Regards
0
 

Author Comment

by:snapper1
Comment Utility
Thank you for your very quick response.
The second one worked first go . The first may also worked but as I was using a named range I only tried the second
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

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…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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

10 Experts available now in Live!

Get 1:1 Help Now