# Minimum and Maximum value of a range

#### UHsoccer

##### Well-known Member
I have dates in column C and interactively I can display the minimum, maximun, average ets by right clicking in the status bar and it displays the information in the Status bar

How can I duplicate those functions with a VB macro for use in my program

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### UHsoccer

##### Well-known Member

I have seen reference to code like

Application.Maximum ......

#### UHsoccer

##### Well-known Member
Re: Minimum and Maximum value of a range - Solved

Here is the code

Code:
``````dim maxDate as Date
maxDate = Application.Max(Sheets(sheetName).Range("A2:A100"))``````

#### hiker95

##### Well-known Member
UHsoccer,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the macro code and pressing the keys CTRL + C
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. And, paste the copied code into VBAProject, Microsoft Excel Objects, ThisWorkbook (on the right pane) by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Code:
``````Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.StatusBar = False
If TypeName(Selection) = "Range" Then
Workbook_SheetSelectionChange Sh, Selection
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim s As String
Dim wfn As WorksheetFunction
Set wfn = Application.WorksheetFunction
On Error GoTo errH:
If wfn.Count(Target) < 2 Then
s = ""
Else
s = "Sum=" & wfn.Sum(Target) & " " & _
"Avg=" & wfn.Average(Target) & " " & _
"Min=" & wfn.Min(Target) & " " & _
"Max=" & wfn.Max(Target) ' etc
End If
errH:
Application.StatusBar = s
End Sub``````

Add several worksheets containing a series of numbers in a range. Then save the workbook.

Click on one of the sheets, and highlite a range of cells with numbers, and look on the left side of the Status Bar.

Last edited:

Replies
1
Views
82
Replies
1
Views
97
Replies
7
Views
271
Replies
2
Views
231
Replies
3
Views
135

1,127,873
Messages
5,627,399
Members
416,245
Latest member
Xterminat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back