selecting rows dynamically in VBA

Question

I want to pick the number of rows dynamically for my standard deviation calculation in VBA.

Currently I have the following code:

Sub Stdev()

Dim x as integer
x=10
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "=STDEV(R[1]C:R[5]C)"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "=STDEV(R[1]C:R[x]C)"'this is my attempt at dynamic calculation
End Sub

How do I correct it to select the number of rows as specified in x?

Peter Albert · Accepted Answer · 2013-02-05 20:17:09Z

1

Try:

Range("E1").FormulaR1C1 = "=STDEV(R[1]C:R[" & x & "]C)"

answered Feb 5, 2013 at 20:17

Peter Albert

17.6k6 gold badges68 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1155299 Over a year ago

if I want to make it a function instead of a sub, how do I do that?

Peter Albert Over a year ago

what do you want as input and as output of the function?

user1155299 Over a year ago

As input, I want the flexibility to select random range of rows (say 1 to 30 or 4 to 19 etc., so I pass the beginning and ending row number as parameters to the function), and I want to get the standard deviation as output of that specified range of cells.

Collectives™ on Stack Overflow

selecting rows dynamically in VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related