Auto Filter stuck again.

  • Why does this not work.

    Selection.AutoFilter field:=AFCC, Criteria1:=Celsite
    freqCnt = Selection.Application.Max(Columns(AFCF))
    freqCnt = Application.Max(3, Columns(AFCF)).SpecialCells(xlCellTypeVisible)

    the last two statements are my attempt to find the maximum value in a filtered range.

    Everything I try comes up with an error

    I want to know the maximum value in the visible cells for the column of my choosing after I filter the data.

    Any Ideas

  • Hi E,

    How about using the SubTotal function?

    Something like this:

    The example finds the maximum figure in column C (ie offset of 2 from the filtered column).


  • OK that works.

    Stupid instructions from excel told be the below info.

    was driving me nutz.

    1 Automatic
    2 Sum
    3 Count
    4 Average
    5 Max
    6 Min
    7 Product
    8 Count Nums
    9 StdDev
    10 StdDevp
    11 Var
    12 Varp

    After reading through you post a few times I picked up on it.

    Although I reduced your script down to this.

    Selection.AutoFilter field:=AFCC, Criteria1:=Celsite
    freqcnt = Application.Subtotal(4, Columns(AFCF))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!