I am trying to write a function that automatically deletes the minimum value for a given selection of cells. I know how to find the minimum value but I just don't know how to delete that value.
Here's what I've got.
Function MinDel(Stuff)
MinDel = Application.Worksheetfunction.min(stuff)
End Function
How do I delete the MinDel value?
You could modify the function from here like that
Option Explicit
Function AddressOfMax(rng As Range) As Range
Set AddressOfMax = rng.Cells(WorksheetFunction.Match(WorksheetFunction.Max(rng), rng, 0))
End Function
Function AddressOfMin(rng As Range) As Range
Set AddressOfMin = rng.Cells(WorksheetFunction.Match(WorksheetFunction.Min(rng), rng, 0))
End Function
Sub TestIt()
Dim rg As Range
Dim rgMin As Range
Set rg = ActiveSheet.Range("A1:A6")
Set rgMin = AddressOfMin(rg)
rgMin.Clear
End Sub
As mentioned in the comments, a UDF (User Defined Function) cannot change a value or range in Excel by default and a Sub should be used. This is my way to delete the minimal value in the Selection:
Public Sub DeleteMinimum()
Dim myRange As Range
Dim minValue As Double
Dim myMin As Range
If Not TypeOf Selection Is Excel.Range Then Exit Sub
Dim valueAssigned As Boolean: valueAssigned = False
minValue = 0
For Each myRange In Selection
If IsNumeric(myRange) Then
If Not valueAssigned Then
valueAssigned = True
minValue = myRange
Set myMin = myRange
Else
If myRange < minValue Then
minValue = myRange
Set myMin = myRange
End If
End If
End If
Next myRange
If Not myMin Is Nothing Then
myMin = "DELETED!"
End If
End Sub
If Not TypeOf Selection Is Excel.Range should probably be handled, and myMin = would be more explicit if it were myMin.Value = ;-)
TypeOf selection is quite a good idea indeed, I have forgotten someone can select a Shape in Excel. For MyMin.Value = I somehow dislike using .Value on a Range, because it looks like additional noise to me. Is there a case when .Value is really needed or the idea that assigning a String to a Range variable is not a good practice?Range is an object, and it's LHS of an assignment operator; such implicit code is why people get confused about when & why a Set keyword is required, and what it means to assign an object reference. Explicit code says something and does exactly that. Much is going on during an implicit default member assignment, esp. given Range.[_Default] being the default member. If the reader can explain what's going on, then fine. Otherwise, it's toxic IMO..Value is some slowness I can live with. Just wondering, why do you know that Range.[_Default] is the default member of Range? In what scenario did you need to check this? (It is the default member): stackoverflow.com/questions/32996772/…The procedure below will delete the lowest value in a selection, provided that the selection comprises more than one cell. It ignores all but the first column of the selection.
Sub DelMin()
' 05 Jan 2019
Dim Arr As Variant, i As Integer
Dim Mm As Variant, m As Integer
With Selection
If .Cells.Count > 1 Then
Arr = .Value
For i = 1 To UBound(Arr)
If Not IsEmpty(Arr(i, 1)) Then
If IsEmpty(Mm) Or (Arr(i, 1) < Mm) Then
Mm = Arr(i, 1)
m = i
End If
End If
Next i
.Cells(m, 1).ClearContents
End If
End With End Sub
Sub/ macro instead?WorksheetFunction.Minhere, because you're not interested in the result ofMin(stuff): it's the cell that contains this value that you're after, not the value itself. So you want theRangeobject with the smallest value in the providedstuff, assuming thisstuffis aRange, which would be much more explicit e.g. if the function signature wasPublic Function DeleteMinValue(ByVal stuff As Range), we'd know it wants aRangeand not just some array of values. Iterate (For Each) the cells instuff, yield a reference to the one with the smallest value.