How to select everything EXCEPT a single row in Excel

[peterkraska]

I have a data set of some 7000+ rows, and i need to be able to
determine the standard deviation of a single row, then determine the
standard deviation of all the rest of the data combined. this then
needs to be replicated for every single row. If i can just have
someone give me an example of how to select everything EXCEPT a single
row that would be perfect! any help would be a life saver! thanks in
Advance!

example.
row 1
row 2
row 3
row 4

so for the first standard deviations it would be the standard
deviation of row 1, then ffiguring out the standard deviation of rows
2 through 4.
the second would be the standard deviation of row 2, then the standard
deviation of rows 1, 3, and 4.
I hope this helps!

 

[Barb Reinhardt]

I'm going to assume that Row 2 is your second row of data and the data is in
columns A-E. The std for the row will be in column F and the std for all
other columns will be in row G. I'm also going to assume that you have data
in column A.

Sub Testing()
Dim myRange As Range

lrow = Cells(Rows.Count, 1).End(xlUp).Row

Set myRange = Cells(2, 1).Resize(lrow - 2 + 1, 1)
Debug.Print myRange.Address

For Each r In myRange
r.Offset(0, 5).FormulaR1C1 = "=stdev(R" & r.Row & "C1:R" & r.Row & "C" &
"5)"
If r.Row > myRange.Row Then
r.Offset(0, 6).FormulaR1C1 = "=stdev(R2C1:R" & r.Row - 1 & "C5,r" &
r.Row + 1 & "C1:R" & lrow & "C5)"
Else
r.Offset(0, 6).FormulaR1C1 = "=stdev(R" & r.Row + 1 & "C1:R" & lrow
& "C5)"
End If

Next r

End Sub


HTH,
Barb Reinhardt