VBA Auto filter with Dynamic Field

[Jenya]

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!

 

[RJPotts]

Hi,

Something like;

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

 

[RJPotts]

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

 

[Jenya]

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

 

[RJPotts]

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;

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

 

[Jenya]

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

 

[RJPotts]

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

Selection.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp

Best regards
Richard

 

[Jenya]

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</pre>
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!

 

[RJPotts]

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..

Selection.Offset(1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp

Hmmmmm, could've sworn thats what I've used in the past..... Maybe try

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.

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;

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

will do the job.

Best regards
Richard

 

[dopikate]

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!