# Iterate through columns

• ### Question

• Hi all,

I have a spreadsheet of data.  For each column I need to do different statistical analysis for.  Now I know you can iterate through the different rows using a code like this:

rowmax = Worksheets("Serial Numbers").Range("B65536").End(xlUp).row
For rowindex = 3 To rowmax
Sheets("Stats").Select
Worksheets("Serial Numbers").Range("B" & rowindex).Copy
Sheets("Stats").Range("C3").PasteSpecial xlPasteValues

Next

And it will go down and look at each row one by one.  But now instead of doing this for rows, I want to go column by column.  For the code to look at Column A and do some equations and what not and when done with column A will then move onto column B and so on.  Does anyone have any way that I can do this?

Friday, February 6, 2015 9:27 PM

### All replies

• You could use code like this:

Dim ColIndex As Long
Dim ColMax As Long
Dim ws As Worksheet
Set ws = Worksheets("Serial Numbers")
' Last used column in row 3
ColMax = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column
' Loop from column 2 (B) to the last used one
For ColIndex = 2 To ColMax
ws.Cells(3, ColIndex).Copy
Worksheets("Stats").Range("C3").PasteSpecial Paste:=xlPasteValues
...
Next ColIndex

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Friday, February 6, 2015 9:44 PM
• How does this look?

Dim r As Integer
Dim ColIndex As Long, ColMax As Long
Dim sum As Double, mean As Double
Dim ws As Worksheet

Set ws = Worksheets("Stats")

ColMax = ws.Cells(8, ws.Columns.Count).End(xlToLeft).Column
rowmax = ws.Range("C65536").End(xlUp).row

For ColIndex = 9 To ColMax
sum = 0
For r = 8 To rowmax
sum = ws.Cells(r, ColIndex).Value + sum
Next r
mean = sum / (rowmax - 7)
ws.Cells(ColIndex, rowmax + 1).Value = mean

Next ColIndex

End Sub

Friday, February 6, 2015 10:22 PM
• You could use the built-in worksheet function Average:

```    Dim RowMax As Long
Dim ColIndex As Long, ColMax As Long
Dim ws As Worksheet
Set ws = Worksheets("Stats")
ColMax = ws.Cells(8, ws.Columns.Count).End(xlToLeft).Column
RowMax = ws.Range("C" & ws.Rows.Count).End(xlUp).Row
For ColIndex = 9 To ColMax
ws.Cells(ColIndex, RowMax + 1).Value = _
Application.Average(ws.Range(ws.Cells(8, ColIndex), _
ws.Cells(RowMax, ColIndex)))
Next ColIndex
```

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Friday, February 6, 2015 10:54 PM
• Is there a built-in worksheet function that will do the standard deviation for me?
Friday, February 6, 2015 11:03 PM
• Yes: Application.StDev_P calculates the population standard deviation of a range, and Application.StDev_S the sample standard deviation.

See https://msdn.microsoft.com/en-us/library/office/ff822194.aspx for a list of worksheet functions available in VBA.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Friday, February 6, 2015 11:37 PM