0

I am working on a code that selects a range of cells but I cannot figure out how to find the maximum value between the selected cells only.

Any idea on how to fix this? Here is what I have so far: 

Function MaxContribution(ws As Worksheet)

    Dim max As Double


    ws.Range("$B$16:$D$1000").AutoFilter Field:=1, Criteria1:="="
    Range("D17:D1000").Select
    max = Application.WorksheetFunction.max(activerange)

    MaxContribution = max



End Function
YaraK
  • 47
  • 6
  • 1
    You want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Jul 18 '19 at 15:45
  • What's `activerange`? Btw - MAX still considers cells that are not visible, if that's what you're trying to avoid. – BigBen Jul 18 '19 at 15:46
  • Also you will not be able to pass a disjointed range, which is what you will get when using the filter, to `Max`. You will need to iterate the areas of the range and test the max of that area against a variable that stores the current max. – Scott Craner Jul 18 '19 at 15:49
  • 1
    is there a way for me to find the max value with only the filtered out values? – YaraK Jul 18 '19 at 15:50
  • 1
    [`SUBTOTAL`](https://support.office.com/en-gb/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939)? – BigBen Jul 18 '19 at 15:51
  • 1
    `max = application.Subtotal(4,ws.Range("$D$16:$D$1000"))` – Scott Craner Jul 18 '19 at 15:55
  • 1
    it worked, thanks a lot – YaraK Jul 18 '19 at 16:03

0 Answers0