How can I find a minimum value in a range of data in excel

Question

How can I find a minimum value in a range of data in excel, select the select first to see where it is and then omit that value from the range and then do this process again for the next minimum value? so that in the end I can omit for example 100 minimum values from a range of 1000 values

Answer 1

Hi, not exactly sure what you are trying to do but he below might give you some ideas:


Public Sub test(rng As Range)
Dim minValue
Dim lngCounter As Long

lngCounter = 0
While lngCounter < 100
    minValue = Application.WorksheetFunction.Min(rng)
   
    Debug.Print minValue
    For Each c In rng
        If c.Value = minValue And lngCounter < 100 Then
            c.ClearContents
             lngCounter = lngCounter + 1
        End If
    Next
Wend
End Sub

Answer 2

you can try something like

c.copy destination:=Range("L2")

Cheers
Shasur

---

http://www.vbadud.blogspot.com

Answer 3

Hi again Shasur, thanks for your help yesterday, I changed your code to the following but it does not work, I'm foolishly playing with it but it does not understand: cell.select

what I want to do is to find for example 5 minimums in the range D1:D8761. then cut this value and the values in the same row in the B and C columns and paste them all in another sheet.

Sub Claim()
Dim minval As Single, alfa As Range, a, b As Integer

Set alfa = ThisWorkbook.Sheets("Energy Profit").Range("D1:D8761")

a = 1

lngCounter = 0

While lngCounter < 5
minval = Application.WorksheetFunction.min(alfa)

For Each cell In alfa
If cell.Value = minval And lngCounter < 5 Then
cell.Select

cell.Offset(0, -2).Select
ActiveCell.Interior.ColorIndex = 34
ActiveCell.Cut
ThisWorkbook.Sheets("D2").Cells(a, 1).Select
ActiveSheet.Paste

cell.Offset(0, -1).Select
ActiveCell.Cut
ThisWorkbook.Sheets("D2").Cells(a, 2).Select
ActiveSheet.Paste

cell.Select
cell.Cut
ThisWorkbook.Sheets("D2").Cells(a, 3).Select
ActiveSheet.Paste

a = a + 1

lngCounter = lngCounter + 1
End If
Next
Wend

End Sub

Answer 4

Hi

Instead of

cell.Select

cell.Offset(0, -2).Select
ActiveCell.Interior.ColorIndex = 34
ActiveCell.Cut
ThisWorkbook.Sheets("D2").Cells(a, 1).Select
ActiveSheet.Paste

You can try the following

cell.Offset(0, -2).Cut Destination:=ThisWorkbook.Sheets("D2").Cells(a, 1)

Cheers
Shasur

---

http://www.vbadud.blogspot.com