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