Newbie VBA Question; Deleting minimum value row
 

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).Entire Row.Delete

FilterRange.AutoFilter

End Sub

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

Regards Paul Edwards

Newbie VBA Question; Deleting minimum value row
 

See if this works

Sub delminrow3() 'smallest0
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

"Paul Edwards" wrote in message
m...
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).Entire Row.Delete

FilterRange.AutoFilter

End Sub

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

Regards Paul Edwards

Newbie VBA Question; Deleting minimum value row
 

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" wrote in message ...
See if this works

Sub delminrow3() 'smallest0
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

"Paul Edwards" wrote in message
m...
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).Entire Row.Delete

FilterRange.AutoFilter

End Sub

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

Regards Paul Edwards