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.

Macro : seeking max value after an Autofilter
 

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

HTH

TJ

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.

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

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

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

.