Excel VBA: How to find max/min of a range while ignoring error cells

How can I use VBA in Excel to determine the max/min of a range that contains error cells such as #N/A or empty cells? I know this is a fairly easy task to conquer with Excel array formulas using something like =MIN(IF(A1:A10="#N/A"))

but I would very much like to accomplish this using VBA.

I'm dealing with several thousand lines of data so the speediest solution would be preferred.

Thank you so much!

Answers


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

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

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

Need Your Help

How to code “git commit” in libgit2?

libgit2

I have searched Google and Stackoverflow for an answer to the question of how to code the equivalent of

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.