Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro : seeking max value after an Autofilter
Dear Experts
My excel sheet encloses 2 columns - 1st column is a zip code with Data/Filter/Autofilter on - 2nd column is a numbe In a macro : - I filter data of the1st colum using the autofilter (i.e. Selection.AutoFilter Field:=1, Criteria1:="10080" - I want then to get the max of the resulting numbers in the 2nd one but how can I specify I want the max of the subset of data and not the max of the full data enclosed in the column (i.e. when no filter is not set on 1st column) Any help greatly appreciated Regards Jerome. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro : seeking max value after an Autofilter
You can use the SpecialCells method of the Range object
and use the xlCellTypeVisible type. HTH TJ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro : seeking max value after an Autofilter
Jerome,
Create a formula on the worksheet using the SUBTOTAL function. If your data is in Column A and B with headers in Row 10 and the data in Rows 11 to 100 then: B9: =SUBTOTAL(4,B11:B100) The value in Cell B9 will recalculate after each AutoFilter and will only calculate based upon the displayed data. The "4" in the function indicates to calculate the MAX of the data. See the Excel Help on SUBTOTAL for more details. Troy "Jerome" wrote in message ... Dear Experts, My excel sheet encloses 2 columns : - 1st column is a zip code with Data/Filter/Autofilter on. - 2nd column is a number In a macro : - I filter data of the1st colum using the autofilter (i.e. Selection.AutoFilter Field:=1, Criteria1:="10080") - I want then to get the max of the resulting numbers in the 2nd one but how can I specify I want the max of the subset of data and not the max of the full data enclosed in the column (i.e. when no filter is not set on 1st column) ? Any help greatly appreciated. Regards, Jerome. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro : seeking max value after an Autofilter
There may be a better way, but one way to do it would be
to copy special, visible and put the copy somewhere. Find your max on the copied data, store it in a variable, then erase the copied data. -----Original Message----- Dear Experts, My excel sheet encloses 2 columns : - 1st column is a zip code with Data/Filter/Autofilter on. - 2nd column is a number In a macro : - I filter data of the1st colum using the autofilter (i.e. Selection.AutoFilter Field:=1, Criteria1:="10080") - I want then to get the max of the resulting numbers in the 2nd one but how can I specify I want the max of the subset of data and not the max of the full data enclosed in the column (i.e. when no filter is not set on 1st column) ? Any help greatly appreciated. Regards, Jerome. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro : seeking max value after an Autofilter
I can select using Range("B:B").SpecialCells(xlCellTypeVisible).Selec t 1/ Is it correct to write Range("B:B") to specify I want to select the column B 2/ How can I get the max of the selected data now Thanks for your help Jerome ----- tinyjack wrote: ---- You can use the SpecialCells method of the Range object and use the xlCellTypeVisible type HT T |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro : seeking max value after an Autofilter
There is no need to use the .Select method, in most cases
you can achieve your desired results by directly dealing with the objects, so you can use the following Dim dblAnswer As Double Dim rngVisible As Range Set rngVisible = Range("B:B").SpecialCells (xlCellTypeVisible) dblAnswer = Application.WorksheetFunction.Max(rngVisible) HTH TJ -----Original Message----- I can select using Range("B:B").SpecialCells (xlCellTypeVisible).Select 1/ Is it correct to write Range("B:B") to specify I want to select the column B ? 2/ How can I get the max of the selected data now ? Thanks for your help. Jerome. ----- tinyjack wrote: ----- You can use the SpecialCells method of the Range object and use the xlCellTypeVisible type. HTH TJ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro, AutoFilter... | Excel Discussion (Misc queries) | |||
Autofilter Macro? | Excel Discussion (Misc queries) | |||
AutoFilter Macro | Excel Worksheet Functions | |||
Macro using autofilter | Excel Discussion (Misc queries) | |||
Keep autofilter after macro is run | Excel Worksheet Functions |