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:
Sub Test()
Dim rngToTest As Range
Dim iCol As Integer
iCol = 2
'offset from column being filtered
Application.ScreenUpdating = False
With Sheet1
Set rngToTest = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
rngToTest.AutoFilter Field:=1, Criteria1:="1"
MsgBox "Max is " & Application.WorksheetFunction _
.Subtotal(4, .AutoFilter.Range.Offset(, iCol))
'4=Max (See Excel help for full list of options)
rngToTest.AutoFilter
End With
Application.ScreenUpdating = True
End SubThe example finds the maximum figure in column C (ie offset of 2 from the filtered column).
HTH
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))
Don’t have an account yet? Register yourself now and be a part of our community!