SpecialCells for only numeric values?

[JFamilo]

I have a range of cells that I wish to find the max and min of. The only problem is that occasionally this range has a vbString or vbError in it. Is there a way to use SpecialCells so that only the numbers are evaluated? Right now my max and min are both coming back as #N/A. Thanks.

[Leith Ross]

Hello JFamilo,

Here is a macro I wrote that does what you are looking for...
_________________________________________________________________

Public Function MinMax(ByVal Cell_Range As String)

'Ignores Cells that are Not Numbers including Dates and Errors
'Variable that will hold MinMax Return Value must be a Variant
'The Function Returns a Variant Array
'The Min Value is in Return Variable(0)
'The Max Value is in Return Varaible(1)

'Example:
' MM = MinMax("A1:G35")
' MinValue = MM(0)
' MaxValue = MM(1)

Dim M(1)
Dim I As Long
Dim J As Long

With Range(Cell_Range)
FirstColumn = .Column
LastColumn = .Columns.Count + FirstColumn - 1
FirstRow = .Row
LastRow = .Rows.Count + FirstRow - 1
M(0) = .Cells(1, 1).Value
M(1) = N
End With

For J = FirstColumn To LastColumn
For I = FirstRow + 1 To LastRow
CellValue = Cells(I, J).Value
If WorksheetFunction.IsNumber(CellValue) Then
If M(0) > CellValue Then
M(0) = CellValue
End If
If M(1) < CellValue Then
M(1) = CellValue
End If
End If
Next I
Next J

MinMax = M

End Function
_________________________________________________________________

Enjoy,
Leith Ross

[JFamilo]

Thank you this works like a charm!