Min and Max value ignoring blank cells

[haseft]

hi,
want to get Min and Max value ignoring blank cells,
the code bellow not works,

Sub Makro5()
'
Dim rng As Range
Dim sht As Worksheet
Dim lastrow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row '

For i = 2 To lastrow
    Set rng = Range("AG" & i & ",AI" & i)
    sht.Range("AP" & i).Value = Application.WorksheetFunction.Min(rng)
    sht.Range("AQ" & i).Value = Application.WorksheetFunction.Max(rng)
Next i

End Sub

 

[Fluff]

In what way doesn't it work?

 

[haseft]

In what way doesn't it work?

se bellow

Data Year1	Data Year2	Data Year3		Result Min	Result Max
2017	2018	2019		2017​	2019​
	2021	2022		2022 (the answer is 2021)​	2022​
		2022		2022​	2022​
	2015	2018		2018​	2018​
2017	2020			2017​	2017​
				0​	0​
2016				2016​	2016​
2018		2024		2018​	2024​
2015	2018			2015​	2015​
				0​	0​

 

[Joe4]

Are you only checking columns AG and AI, or do you also want to include column AH?
If you want to include column AH, I believe that you need to change this line:

    Set rng = Range("AG" & i & ",AI" & i)

to this:

    Set rng = Range("AG" & i & ":AI" & i)

Subtle difference, but it makes all the difference!

 

[haseft]

Are you only checking columns AG and AI, or do you also want to include column AH?
If you want to include column AH, I believe that you need to change this line:

    Set rng = Range("AG" & i & ",AI" & i)

to this:

    Set rng = Range("AG" & i & ":AI" & i)

Subtle difference, but it makes all the difference!

you are right,
it works,
but if all 3 cells are empty the result is 0 (zero), why? the result must be empty,

 

[Joe4]

That is way that the MIN and MAX functions work because they have to return numbers. If you apply it to an empty range, it will return a 0.
If you do not want it to return anything if the range is empty, then check the range first for entries, i.e.

Sub Makro5()
'
Dim rng As Range
Dim sht As Worksheet
Dim lastrow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row '

For i = 2 To lastrow
    Set rng = Range("AG" & i & ":AI" & i)
    If Application.WorksheetFunction.Count(rng) > 0 Then
        sht.Range("AP" & i).Value = Application.WorksheetFunction.Min(rng)
        sht.Range("AQ" & i).Value = Application.WorksheetFunction.Max(rng)
    End If
Next i

End Sub

 

[haseft]

That is way that the MIN and MAX functions work because they have to return numbers. If you apply it to an empty range, it will return a 0.
If you do not want it to return anything if the range is empty, then check the range first for entries, i.e.

Sub Makro5()
'
Dim rng As Range
Dim sht As Worksheet
Dim lastrow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row '

For i = 2 To lastrow
    Set rng = Range("AG" & i & ":AI" & i)
    If Application.WorksheetFunction.Count(rng) > 0 Then
        sht.Range("AP" & i).Value = Application.WorksheetFunction.Min(rng)
        sht.Range("AQ" & i).Value = Application.WorksheetFunction.Max(rng)
    End If
Next i

End Sub

Thanks Joe4,
it works perfect ??

 

[Joe4]

You are welcome.
Glad we were able to help!