CODEWITHSUNDEEP
excelvba
dolecek21
dolecek21

Reputation: 33

Min of non-contiguous range in VBA Excel

I am looking to find a minimum value of a non-contiguous range using Excel VBA. The main reason for the range being non-contiguous is that I want the user to be able to select the range. Unfortunately I haven't been able to find anything that fits my situation.

Specifically, for the following two sets of data, the user could select cells A1:A5, hit control, and then select cells A7:A11. The macro should return the minimum values in column C for all cells in column B selected (all of them in this example). 

     A     B
1    1    0.1
2    2    2.0 
3    3    3.0
4    4    4.0 
5    5    5.0

6    6    0.05
7    7    0.2
8    8    0.3
9    9    0.4
10   10   0.5

The following is what I have been using, but it only returns the minimum of the first, contiguous portion of the range.

Sub testa()

Dim RngA, out As Range
Dim MSA As Single


    Set RngA = Range(Selection.Address)
    MsgBox RngA.Address
    MSA = WorksheetFunction.Min(RngA.Offset(0, 1).Value)

    Set out = Worksheets("summary").Range("C13")

    out = MSA

End Sub

The MsgBox is just to verify that the range is being selected correctly. It seems that it is correct ($A1:$A5, $A7:&A11). The output, located in the "summary" sheet, only returns the minimum of the first block of data, 0.10. The results should be 0.05 (cell B7).

I thought about running a loop to find the minimum for each portion of the range, but I'm not sure how I would go about splitting the "Selection.Address." Does anyone have a suggestion as to how to get the macro to return the minimum value for the entirety of the non-contiguous range?

Upvotes: 3

Views: 1822

Answers (1)

Frank
Frank

Reputation: 454

The MIN worksheet function works just fine for non-contiguous ranges and I have been unable reproduce your problems. The following code works as expected:

Sub test_min_in_non_contiguous_range()
    Dim area As Range: Set area = ActiveSheet.Range("$A$1:$A$3, $A$5:$A$11")
    Debug.Print Application.WorksheetFunction.Min(area)
End Sub

Let me know if you run into some trouble with this :)

Edit: Just realised what the problem is in your code.

MSA = WorksheetFunction.Min(RngA.Offset(0, 1).Value)

should be 

MSA = WorksheetFunction.Min(RngA.Offset(0, 1))

Upvotes: 1

Related Questions