Dynamic Field:= in Autofilter

[Spichael]

Hi there, I have the following code for a report. This code takes 60 plus fields and hides a bunch of them, autofilters based on criteria and sorts. My issue is that the source data changes from time to time which would throw off my filters. I take care of this in other areas by using the CreateNames in the top of the code and the Case statements in the middle of the code. But I cannot find a way to cover this in the AutoFilter Field:=1 area. I would like it to say something like Field:=ANR or something similar. The ANR column will always be in column AH. I appreciate your assistance.

Private Sub EDUANR_Click()
Dim c As Range
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Range("B1").Select
    Selection.CurrentRegion.Select
    Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
  
    Cells.EntireColumn.AutoFit
    
    For Each c In Range("B1:" & Range("B1").End(xlToRight).AddressLocal(False, False))
        Select Case c.Value
        Case Is = "MI Trace (709)"
            c.ColumnWidth = 15
        Case Is = "House Bill"
            c.ColumnWidth = 15
        Case Is = "Container Number"
            c.ColumnWidth = 15
        Case Is = "Origin"
            c.ColumnWidth = 8
        Case Is = "Vessel and Voyage"
            c.ColumnWidth = 24
        Case Is = "Carrier"
            c.ColumnWidth = 40
        Case Is = "Consignee"
            c.ColumnWidth = 40
        Case Is = "EDU"
            c.ColumnWidth = 10
        Case Is = "ANR"
            c.ColumnWidth = 10
        Case Else
            c.ColumnWidth = 0
        End Select
    Next c
    Range("B1").Select
    ActiveSheet.AutoFilterMode = False
    Range("B1").AutoFilter Field:=37, Criteria1:="<>"
    Range("B1").AutoFilter Field:=33, Criteria1:="="
    
     Range("B1:BZ1782").Sort Key1:=Range("Vessel_and_Voyage"), Order1:=xlAscending,     Key2:=Range("EDU"), Order1:=xlAscending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
        , DataOption1:=xlSortNormal
End Sub

 

[Spichael]

Is anyone able to help on this one????

 

[Richard Schollar]

Hi

I guess I don't really understand the problem you are facing as if your data fills columns A:AH (at least) then the ANR column is always going to be Field 34 isn't it?

 

[Spichael]

Thanks for the reply. It will not always be Field 34. It might be 32 one day or 38 another based on is new field source data has been added. That is why everything else is dynamic except this one part.

 

[Richard Schollar]

I would like it to say something like Field:=ANR or something similar. The ANR column will always be in column AH.

So you want to filter the ANR column but it won't always be in column AH in contrast to what your original post said?

 

[Spichael]

You are kind of correct. I am using the following to assign names to each column everytime I run the report. So this creates names for each column and if it is in a new location it does not matter, since I am using the named range it works regardless.

    Range("B1").Select
    Selection.CurrentRegion.Select
    Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False

 

[Richard Schollar]

Ah well if you know you have a single column range named AHR and you need the ordinal number of the column you can use this to return it:

Field:=Range("AHR").Column

Have I interpreted what you want correctly here?

 

[Spichael]

You have interpreted it and it is working in some macros and not others. I just need to figure this part out. I am trying to tweak things and I will let you know. I really appreciate your help.

 

[Spichael]

This code works

Range("B1").AutoFilter Field:=Range("LOG").Column, Criteria1:="="

but this one does not

Range("B2").AutoFilter Field:=Range("Origin").Column, Criteria1:="XIC"

The last ones just pulls another column even though things looks properly names and referenced.