VBA to calculate Standard Deviation

**schmidtkicker** · 11-11-2014, 05:33 AM

Hello,

I'm trying to build a macro (that I will assign to a command button) that will calculate the standard deviation of a range of values. The issue I'm running into is that the number of values to be included in the formula may change, and I don't want to have to go back and edit the code to incorporate all values. Long story short: I have the below code which calculates the standard deviation of the values from C3 to C106. How do I change it so that it will take all the values in column C...if there are more than through C106? Thank you!!

Sub StdDev()
'
' StdDev Macro
'

'
    Range("E8").Formula = "=STDEV(C3:C106)"
    Range("E8").Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0.00%"
End Sub

**romperstomper** · 11-11-2014, 06:17 AM

Assuming there is nothing else in column C below the data:

Sub StdDev()
'
' StdDev Macro
'

'
    Dim LastRow As Long
    LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
    With Range("E8")
        .Formula = "=STDEV(C3:C" & LastRow & ")"
        .NumberFormat = "0.00%"
    End With
End Sub