All geek questions in one place

Excel VBA: how to find maximum / minimum range when ignoring error cells

How can I use VBA in Excel to determine the maximum / minimum range containing error cells, such as #N/A or empty cells? I know this is a fairly simple task - to conquer Excel formulas using something like =MIN(IF(A1:A10="#N/A"))

but I would really like to do this using VBA.

I am dealing with several thousand rows of data, so it would be the fastest solution.

Thank you very much!

+4
source share
1 answer

You can use Evaluate or a shortcut [] to return the equivalent of a VBA formula

So the Excel array formula
=MIN(IF(NOT(ISNA(A1:A10)),A1:A10)) can be used in type code

 Sub Test() MsgBox [MIN(IF(NOT(ISNA(A1:A10)),A1:A10))] End Sub
+12
source

Source: https://habr.com/ru/post/1433085/

More articles:


All Articles