# Looping through a column, selecting range after range

#### emdee

##### New Member
Ok.. what I'm trying to do:
My observations, around 4000 per column, are on sheet 1. Starting with the first column, I want to calculate the population variance (VARP) of each set of 21 observations (range of +20 rows down), put the result on sheet 2 and then do the same for the next 21, put the result below the first result on sheet 2 and so on until the observations run out. This should be done for all the columns.

I tried building the inner loop for calculating VARP but I'm not sure how to increment and select the range of 21 rows to feed to VARP. Any help here would be highly appreciated!

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Andrew Poulsom

##### MrExcel MVP
Could you post your current code?

#### emdee

##### New Member
Here's my (obviously) non-working code so far. Hope you get from here what I'm trying to accomplish. I have Dimmed those variables as ??? cause I don't know the right type.

Code:
``````'Dim rangestart As ???
'Dim rangeend As ???
Dim rangetocalc As Range

rangeend = rangestart + rangestart.Offset(20, 0)
Worksheets("variances").Range("B1").Activate

For i = 0 To 200
rangetocalc = Range("rangestart:rangeend")
Worksheets("variances").ActiveCell.Offset(i, 0).Value = Application.WorksheetFunction.VarP(rangetocalc)
rangestart = rangeend + rangeend.Offset(1, 0)
rangeend = rangestart + rangestart.Offset(20, 0)
Next i``````

#### Andrew Poulsom

##### MrExcel MVP
Try:

Code:
``````Sub Test()
Dim Sh1 As Worksheet
Dim Rng1 As Range
Dim Arr1() As Variant
Dim Sh2 As Worksheet
Dim i As Long
Dim r As Long
Dim c As Integer
Dim j As Long
Dim Arr2() As Variant
Set Sh1 = Worksheets("Sheet1")
Set Rng1 = Sh1.Range("A1").CurrentRegion
ReDim Preserve Arr1(1 To Rng1.Cells.Count)
Set Sh2 = Worksheets("Sheet2")
Sh2.Cells.ClearContents
i = 0
For c = 1 To Rng1.Columns.Count
For r = 1 To Rng1.Rows.Count
i = i + 1
Arr1(i) = Rng1.Cells(r, c).Value
Next r
Next c
ReDim Preserve Arr2(1 To 21)
r = 1
For i = 1 To UBound(Arr1) Step UBound(Arr2)
For j = 1 To UBound(Arr2)
Arr2(j) = Arr1(i + j - 1)
Next j
Sh2.Cells(r, 1) = WorksheetFunction.VarP(Arr2)
r = r + 1
Next i
End Sub``````

#### emdee

##### New Member
Thanks a lot for your help!! I modified it a bit and now it does exactly what I wanted:

Code:
``````Sub Test2()
Dim Sh1 As Worksheet
Dim Rng1 As Range
Dim Arr1() As Variant
Dim Sh2 As Worksheet
Dim i As Long
Dim r As Long
Dim c As Integer
Dim j As Long
Dim Arr2() As Variant
Set Sh1 = Worksheets("Sheet1")
Set Rng1 = Sh1.Range("A1").CurrentRegion
ReDim Preserve Arr1(1 To Rng1.Rows.Count)
Set Sh2 = Worksheets("Sheet2")
Sh2.Cells.ClearContents

For c = 1 To Rng1.Columns.Count
i = 0
For r = 1 To Rng1.Rows.Count
i = i + 1
Arr1(i) = Rng1.Cells(r, c).Value
Next r
ReDim Preserve Arr2(1 To 21)

r = 1
For i = 1 To UBound(Arr1) Step UBound(Arr2)
For j = 1 To UBound(Arr2)
Arr2(j) = Arr1(i + j - 1)
Next j
Sh2.Cells(r, c) = Application.VarP(Arr2)
r = r + 1
Next i

Next c

End Sub``````

Replies
0
Views
587
Replies
1
Views
220
Replies
11
Views
433
Replies
4
Views
286
Replies
5
Views
268

1,195,655
Messages
6,010,939
Members
441,577
Latest member
Alonshow

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back