MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Efficient Copying Problem

Posted by Yasmin Thomas on November 26, 2001 11:40 AM


I have a very big worksheet with about 2000 lines. In every 20th line is a value that i want to work with. I have to calculate the deviation of these values, but because i cant give excel the formula =dev(b10:b2000) (there are other values between the interesting ones) it would be =dev(b10;b30;b50;b70...) with 100 arguments and excel cant take this.

Is there an easy way to gather the values of each 20th cell in a tight new area on the worksheet?

Tank you in advance!

Posted by John on November 26, 2001 2:13 PM

This will give you every 20th row in Column A, just copy down as needed


This will give you every 20th row in an array formula (enter using Control+Shift+Enter):


Posted by Bariloche on November 26, 2001 9:13 PM


The following code will take every 20th value in column B, starting with cell B10, and put it on a new sheet starting in column A1. The one assumption I've made is that the name of your data sheet is "data".

Sub StepByTwenty()

Dim i As Double
Dim k As Double

Application.ScreenUpdating = False

ActiveSheet.Name = "CalcDeviation"
k = 1

For i = 10 To 2000 Step 20
Sheets("CalcDeviation").Cells(k, 1).Value = Sheets("data").Cells(i, 2).Value
k = k + 1
Next i

Application.ScreenUpdating = True

End Sub

Hopefully this helps.