Newbie VBA Question; Deleting minimum value row

P

Paul Edwards

Hi, I have been struggling with a piece of code to identify the lowest
value in a column, select that value then delete the row record
containing that value.

So far I have:

Sub TEST()

Set FilterRange = Range("E2:E12" & Range("E12").Row)

FilterRange.AutoFilter Field:=1,
Criteria1:="=MIN(IF(E2:E12<>0,E1:E12)) ", Operator:=xlAnd

FilterRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete

FilterRange.AutoFilter

End Sub

This just deletes row 2, my brain is now burnt and would appreciate
ANY help.

Regards Paul Edwards
 
D

Don Guillett

See if this works

Sub delminrow3() 'smallest>0
x = Application.Small([A1:A10], _
Application.CountIf([A1:A10], 0) + 1)
Rows(Application.Match(x, Columns(1), 0)).Delete
End Sub
or
Sub delminrow()'If all >0
x = Application.Match(Application. _
Min(Columns(1)), Columns(1), 0)
Rows(x).Delete
End Sub
 
P

Paul Edwards

Hi Don,
many thanks for your prompt reply, code now works, I was obviously on
the wrong track.
Thank you for your time.

Regards
Paul


Don Guillett said:
See if this works

Sub delminrow3() 'smallest>0
x = Application.Small([A1:A10], _
Application.CountIf([A1:A10], 0) + 1)
Rows(Application.Match(x, Columns(1), 0)).Delete
End Sub
or
Sub delminrow()'If all >0
x = Application.Match(Application. _
Min(Columns(1)), Columns(1), 0)
Rows(x).Delete
End Sub

--
Don Guillett
SalesAid Software
[email protected]
Paul Edwards said:
Hi, I have been struggling with a piece of code to identify the lowest
value in a column, select that value then delete the row record
containing that value.

So far I have:

Sub TEST()

Set FilterRange = Range("E2:E12" & Range("E12").Row)

FilterRange.AutoFilter Field:=1,
Criteria1:="=MIN(IF(E2:E12<>0,E1:E12)) ", Operator:=xlAnd

FilterRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete

FilterRange.AutoFilter

End Sub

This just deletes row 2, my brain is now burnt and would appreciate
ANY help.

Regards Paul Edwards
Click to expand...
Click to expand...
 
Top