Results 1 to 10 of 10

Thread: VBA Auto filter with Dynamic Field
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2012
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Auto filter with Dynamic Field

    Hello guys,

    I am a beginner in VBA and I am trying to do auto-filter with multiple criteria but I don't want to hard code the column number i.e. The field number. In my Macro I want to remove the rows which contain "runrate" in column name Opportunity Name. But this column is going to change every time when I run a different report. Below is what I am trying to do..I think there might be a way of combining those two things so it gives me dynamic field number...

    Sub FindColumnOppName()
    '
    ' Find Column Opportunity Name and filter runrate.
    'Find the column name Opportunity name

    Rows("1:1").Select
    Selection.Find(What:="Opportunity Name", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

    'Filter Column Opportunity name on runrate and run-rate

    ActiveCell.Select
    Selection.AutoFilter Field:=6, Criteria1:=Array( _
    " runrate", " run-rate"), Operator:=xlFilterValues



    End Sub

    THANK YOU IN ADVANCE!

  2. #2
    Board Regular
    Join Date
    Apr 2007
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Auto filter with Dynamic Field

    Hi,

    Something like;

    Code:
    Sub DynamicFilter()
    
    SearchCol = "Test Column Header"
    SearchFor = "C2"
    
    Dim rng1 As Range
    Set rng1 = ActiveSheet.UsedRange.Find(SearchCol, , xlValues, xlWhole)
    
        Range("A1").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=rng1.Column, Criteria1:=SearchFor
    
    End Sub
    should get you started. It will search row 1 for the text specified by 'SearchCol' and will then apply the filter on that column with the filter criteria being specified by 'SearchFor'.

    Best regards
    Richard

  3. #3
    Board Regular
    Join Date
    Apr 2007
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Auto filter with Dynamic Field

    PS, the SearchFor is referring to C2 as text, not as a cell address!

  4. #4
    New Member
    Join Date
    Jul 2012
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Auto filter with Dynamic Field

    Hi,

    Your code worked perfectly!!! Thank you very much!!! The field is dynamic and it works every time!!!

    I have tried to modify your code to add another criateria but when I try to run it it brings up error msg Saying Object required and it highlights i= after Dim i As string..What does it mean? Thanks in advance again!

    Sub DynamicFilter2()
    SearchCol = "Opportunity Name"

    Dim i As String
    Set i = Array( _
    " runrate", " run-rate")

    Dim rng1 As Range
    Set rng1 = ActiveSheet.UsedRange.Find(SearchCol, , xlValues, xlWhole)

    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=rng1.Column, Criteria1:=i
    End Sub

  5. #5
    Board Regular
    Join Date
    Apr 2007
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Auto filter with Dynamic Field

    Quote Originally Posted by Jenya View Post
    Hi,

    Your code worked perfectly!!! Thank you very much!!! The field is dynamic and it works every time!!!

    I have tried to modify your code to add another criateria but when I try to run it it brings up error msg Saying Object required and it highlights i= after Dim i As string..What does it mean? Thanks in advance again!

    Sub DynamicFilter2()
    SearchCol = "Opportunity Name"

    Dim i As String
    Set i = Array( _
    " runrate", " run-rate")

    Dim rng1 As Range
    Set rng1 = ActiveSheet.UsedRange.Find(SearchCol, , xlValues, xlWhole)

    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=rng1.Column, Criteria1:=i
    End Sub
    I couldnt yet get an array to work......

    If it's just ' runrate' & ' run-rate' you're filtering on then what about;

    Code:
    Sub DynamicFilter2()
    SearchCol = "Opportunity Name"
    
    Dim rng1 As Range
    Set rng1 = ActiveSheet.UsedRange.Find(SearchCol, , xlValues, xlWhole)
    
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=rng1.Column, Criteria1:=" runrate", Criteria2:=" run-rate", Operator:=xlOr
    
    End Sub
    Best regards
    Richard

  6. #6
    New Member
    Join Date
    Jul 2012
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Auto filter with Dynamic Field

    Hi Richard,

    I have tried different methods and at the end managed to get this one working with 3 criteria:

    Sub DynamicFilter()

    SearchCol = "Test Column Header"

    Dim rng1 As Range
    Set rng1 = ActiveSheet.UsedRange.Find(SearchCol, , xlValues, xlWhole)

    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=rng1.Column, Criteria1:=Array( _
    "runrate", "run-rate", "runnrate"), Operator:=xlFilterValues



    End Sub


    I have tried yours and it works perfect but when I add another criteria3 it brings up error..Is there a limit of only 2 criteria when using your option?

    Thank you very much for your help!! I have to try different ways now to delete everything that I have filtered on

  7. #7
    Board Regular
    Join Date
    Apr 2007
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Auto filter with Dynamic Field

    Quote Originally Posted by Jenya View Post
    Hi Richard,

    I have tried different methods and at the end managed to get this one working with 3 criteria:

    Sub DynamicFilter()

    SearchCol = "Test Column Header"

    Dim rng1 As Range
    Set rng1 = ActiveSheet.UsedRange.Find(SearchCol, , xlValues, xlWhole)

    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=rng1.Column, Criteria1:=Array( _
    "runrate", "run-rate", "runnrate"), Operator:=xlFilterValues



    End Sub
    Curiously that doesnt work for me..... What version of Excel are you using please?

    I have tried yours and it works perfect but when I add another criteria3 it brings up error..Is there a limit of only 2 criteria when using your option?
    Yes, My method would be limited to two criteria (hence 'If it's just ' runrate' & ' run-rate' you're filtering').

    Thank you very much for your help!! I have to try different ways now to delete everything that I have filtered on
    Code:
    Selection.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    Best regards
    Richard

  8. #8
    New Member
    Join Date
    Jul 2012
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Auto filter with Dynamic Field

    Hi,

    I am using Excel 2010. I have to say that initially it was bringing up error message to me as well but eventually it worked. I have tried to do it couple of time and it worked but I have no idea what was going wrong because it was the same code everytime... This is the code that I copied from my VBA and it works..




    Sub DynamicFilterJenya()

    SearchCol = "Opportunity Name"

    Dim rng1 As Range
    Set rng1 = ActiveSheet.UsedRange.Find(SearchCol, , xlValues, xlWhole)

    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=rng1.Column, Criteria1:=Array( _
    "runrate", "run-rate", "runnrate"), Operator:=xlFilterValues



    End Sub





    The code below deletes everything even the column headers..

    Code:


    Selection.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    Another question I would like to ask is do you know what is the symbol for wildcards in VBA? I know excel uses * but here I have tried and it doesn't work. This code that we wrote filters rows that contain runrate only but if there are couple of other words including runrate it doesn't filter the row.

    Thanks!

  9. #9
    Board Regular
    Join Date
    Apr 2007
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Auto filter with Dynamic Field

    Quote Originally Posted by Jenya View Post
    Hi,

    I am using Excel 2010. I have to say that initially it was bringing up error message to me as well but eventually it worked. I have tried to do it couple of time and it worked but I have no idea what was going wrong because it was the same code everytime... This is the code that I copied from my VBA and it works..
    I'll give that a go on 2007 when I get a chance......

    The code below deletes everything even the column headers..
    Code:
    Selection.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    Hmmmmm, could've sworn thats what I've used in the past..... Maybe try
    Code:
    Sheets("Sheet1").AutoFilter.Range.Offset(1).Delete xlShiftUp
    Another question I would like to ask is do you know what is the symbol for wildcards in VBA? I know excel uses * but here I have tried and it doesn't work. This code that we wrote filters rows that contain runrate only but if there are couple of other words including runrate it doesn't filter the row.
    Code:
    Selection.AutoFilter Field:=rng1.Column, Criteria1:="*runrate*"
    As the arrays wont work on this machine you'll need to apply those yourself, I'd imagine changing your code to;

    Code:
    Selection.AutoFilter Field:=rng1.Column, Criteria1:=Array( _
    "*runrate*", "*run-rate*", "*runnrate*"), Operator:=xlFilterValues
    will do the job.

    Best regards
    Richard

  10. #10
    New Member
    Join Date
    Jul 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Auto filter with Dynamic Field

    Hello everyone!
    I am working with unusual task of excel. I have three sheets. In second sheet there is all data base. In third sheet all criteria according to which I should make a filter. The result of filtering I should see in first sheet. I mixup how to make this task.

    First time I followed technology of recording a macro of Advance Filter with two bottoms: Filtering and the Cleaning on the first sheet, two data: first data with data validation list of criteria and second data shows the result of filtering.
    It worked very well.
    Then my manager decided that it should work if in the row of the criteria (which made by data validation) we could chose multi criteria.
    I've done multi data validation but now advance filter doesn't work. After research of another way I came to the point that I should write an autofilter. But I don't imaging how I should write it in my case…Can somebody PLEASE help me???

    Thanks in Advance!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •