Modifying Root mean Square macro

[bradyj7]

Hi all, below is macro for calculating the root mean square of values in 10 cells. I would like to modify it to calculate the root mean square of all the values in column T. However the number of rows is variable so it would need to loop until the last row.

Thank you in advance

Sub RMS()
Dim rmsval As Variant
i = 0
sumsqr = 0
‘Do loop used to sum squared value (i.e. x1^2+x2^2+x3^2+x4^2…)
Do Until i = 10
sumsqr = sumsqr + Cells(i + 1, 1) ^ 2
i = i + 1
Loop
‘Calculate RMS value
rmsval = (sumsqr / i) ^ 0.5
End Sub

 

[MrKowz]

You could do this with native worksheet formulas, but here is a VBA alternative that should work for you:

Sub RMS()
Dim rmsval  As Double, _
    LR      As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
rmsval = (Application.SumProduct(Range("A1:A" & LR), Range("A1:A" & LR)) / LR) ^ 0.5
MsgBox rmsval
End Sub

 

[bradyj7]

Thank you MrKowz.

John