How to delete a certain value using vba commands?

Question

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?

a UDF can't modify any other cells. it takes input, computes a result, returns that result. looks like you might want to make that a `Sub` / macro instead? — Mathieu Guindon, Jan 05 '19 at 05:39
You don't want to use `WorksheetFunction.Min` here, because you're not interested in the result of `Min(stuff)`: it's the *cell* that contains this value that you're after, not the value itself. So you want the `Range` object with the smallest value in the provided `stuff`, assuming this `stuff` is a `Range`, which would be much more explicit e.g. if the function signature was `Public Function DeleteMinValue(ByVal stuff As Range)`, we'd know it wants a `Range` and not just some array of values. Iterate (`For Each`) the cells in `stuff`, yield a reference to the one with the smallest value. — Mathieu Guindon, Jan 05 '19 at 06:01
Ok, so if I wanted to create a function in excel that when you select an array it prints the minimum value in the cell you entered the function and deleted that value from the array so that the same function can be used more than once how would I do it? I'm really new to this and I just want to delete the damn value. — ZeBeowulf, Jan 05 '19 at 06:23

score 1 · Answer 1 · answered Jan 05 '19 at 06:59

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

Vityata · Answer 2 · 2019-01-06T11:41:26.277

1

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

edited Jan 06 '19 at 11:41

answered Jan 05 '19 at 19:09

Vityata

42,633
8
55
100

1

Not bad, though `If Not TypeOf Selection Is Excel.Range` should probably be handled, and `myMin =` would be more explicit if it were `myMin.Value =` ;-) – Mathieu Guindon Jan 06 '19 at 06:09
@MathieuGuindon - `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? – Vityata Jan 06 '19 at 11:45
1

It's an implicit default member assignment, which makes both the reader and VBA work much harder than they should just to get to that property. `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. – Mathieu Guindon Jan 06 '19 at 15:42
@MathieuGuindon - I see your point, being explicit and making VBA slightly faster is quite good, but `.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): https://stackoverflow.com/questions/32996772/is-value-actually-the-default-property-of-the-range-object – Vityata Jan 06 '19 at 15:53

score 0 · Answer 3 · answered Jan 05 '19 at 08:11

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

How to delete a certain value using vba commands?

3 Answers3