Macro : seeking max value after an Autofilter

J

Jerome

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.
 
T

tinyjack

You can use the SpecialCells method of the Range object
and use the xlCellTypeVisible type.

HTH

TJ
 
T

TroyW

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 said:
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.
Click to expand...
Selection.AutoFilter Field:=1, Criteria1:="10080")
- I want then to get the max of the resulting numbers in the 2nd one but
Click to expand...
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) ?
 
D

David

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
Click to expand...
(i.e. Selection.AutoFilter Field:=1, Criteria1:="10080")
- I want then to get the max of the resulting numbers in
Click to expand...
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) ?
 
J

Jerome

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

HT

T
 
T

tinyjack

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
 
Top