Min continuous range in VBA Excel
I am looking to find the minimum value of a non-contiguous range using Excel VBA. The main reason the range is non-contiguous is because I want the user to be able to select the range. Unfortunately, I could not find anything to match my situation.
Specifically, for the next two datasets, the user can select cells A1: A5, control the tap, and then select cells A7: A11. The macro should return the minimum values in column C for all selected cells in column B (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 used, 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
MsgBox should just check if the range is correct. This seems to be correct ($ A1: $ A5, $ A7: & A11). The output located on the "pivot" worksheet returns only the minimum of the first block of data, 0.10. The results should be 0.05 (cell B7).
I was thinking about running a loop to find the minimum for each part of the range, but I'm not sure how I would go about dividing "Selection.Address". Does anyone have a suggestion on how to get the macro to return the minimum value for the entire undefined range?
source to share
The MIN worksheet function is great for non-contiguous ranges and I haven't been able to 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 have any problems with this :)
Edit: Just figured out that the problem is in your code.
MSA = WorksheetFunction.Min(RngA.Offset(0, 1).Value)
it should be
MSA = WorksheetFunction.Min(RngA.Offset(0, 1))
source to share