Finding out max value from used range

[xsdip]

Hi,

Can anyone please give me some inputs.

I have to find out the maximum value of all the entered values in the excel sheet.

The no of entries in each rows differs. For eg : A column may contain 30 rows. B column contains 40 rows etc.

I used this code. But it takes lot of time. Is the for each loop makes the issue? I am using used range option in for each loop.


For each a in activesheet.usedrange
If b<abs(a) then
b = abs(a)
End if
Next a

When i run this code, I am getting type mismatch error also because since the rows are different in each column.

I think selecting the used cells alone will clear the problem. Can anyone please help on this.

 

[Anthony47]

MaxValue = Application.Max(ActiveSheet.UsedRange)

This should do the job

However... it will return an error if the usedrange includes an error condition.

To skip potential errors we need to move to a more complex solution:

Dim wArr, MaxValue
'
wArr = ActiveSheet.UsedRange.Value
MaxValue = Application.Max(wArr)
If IsError(MaxValue) Then
    For I = 1 To UBound(wArr)
        For j = 1 To UBound(wArr, 2)
            If IsError(wArr(I, j)) Then
                wArr(I, j) = "Z"
            End If
        Next j
    Next I
    MaxValue = Application.Max(wArr)
End If

As you see, we try to get the result from the used range, but if it fails then we scan the array with copy of the used range and remove the error conditions

Bye