VBA autofilter field range

**iktpq** · 11-27-2014, 07:57 AM

Hi,

i have a macro that will set a filter based on set columns

Sub test_filter()


On Error Resume Next
Application.ScreenUpdating = False
    Sheets("Agent Data").Select
    
    ActiveSheet.Range("$A$2:$EC$3061").AutoFilter Field:=4, Criteria1:= _
        "Internal"
        
        
    ActiveSheet.Range("$A$2:$EC$3061").AutoFilter Field:=5, Criteria1:=RGB(255 _
        , 0, 0), Operator:=xlFilterFontColor
        
             With Sheets("Internal sale Trends").DropDowns("Drop Down 1")
    Sheets("Agent data").Range("$A$2:$EC$3061").AutoFilter Field:=127, _
        Criteria1:=WorksheetFunction.Index(Range(.ListFillRange), .Value)

     End With
     
     With Sheets("Internal sale Trends").DropDowns("Drop Down 2")
    Sheets("Agent data").Range("$A$2:$EC$3061").AutoFilter Field:=7, _
        Criteria1:=WorksheetFunction.Index(Range(.ListFillRange), .Value)
End With


     
    
        
Application.ScreenUpdating = True

End Sub

This is working fine for what i need.

however i've been asked to add to it something do it. but it would mean the field would not be set as it depends on a different cell as to what column to filter.

so in another tab in have headings with numbers 1 to 39 from J - AV

in another tab i have the same number ranked to show the top ten, these top 10 will change based on another forumal i have set up.

i want to be able to run the filter that wil do the above code but also filter that question number (i understand i may need to have 10 different bits of code for each of the top 10)

hope this make sense.

**davesexcel** · 11-28-2014, 04:02 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**iktpq** · 11-28-2014, 05:08 AM

Hi Davesexcel

in the attacked workbook the agent data tab is updated from another report.
I've stripped down the report and removed names etc

The trends tab will show the top trends out of 39 questions if they are marked red. based on the week and the manager name. all that is working find.

what I want to show is where it says (3 out of 3 calls) who are the 3. what are the agents names.

I'm not sure I'm over complicating things using the filter macro.

in the agent data tab I have a CF set on the agent name to change the text to red if red is any of the questions 1 - 39

on the trends tab I want to try and put a filter on the question number

so the top question for manager 1 is Q35. I want to filter that question in agent data. but this will change to a another question for a different manager.

I hope I've explained it as best as I could

thanks for the help

EDIT -

i've sorted it out by using the following code

Sub filter()

Dim lngFilterColumn As Long

        lngFilterColumn = Sheets("Internal sale Trends").Range("C37").Value + 9
        Sheets("Agent Data").Range("$J$2:$AV$3061").AutoFilter _
            Field:=lngFilterColumn, Criteria1:="Red"