Hi John,

Am Sat, 30 Aug 2014 08:42:19 +0100 schrieb Saxman:

Ideally, these are the columns to include.

X2:X

Z2:Z

AA2:AA

AB2:AB

AC2:AC

AD2:AD

AE2:AD

AK2:AK

AL2:AL

AM2:AM

AN2:AN

AO2:AO

AP2:AP

AQ2:AQ

AX2:AX

try:

Sub myAvg()

Dim LRow As Long, i As Long, n As Long

Dim Start As Long

Dim rngC As Range

Dim dblAvg As Double

Dim strCol As String, arrCol As Variant

strCol = "X,Z,AA,AB,AC,AD,AE,AK,AL,AM,AN,AO,AP,AQ,AX"

arrCol = Split(strCol, ",")

With ActiveSheet

For n = LBound(arrCol) To UBound(arrCol)

LRow = .Cells(Rows.Count, arrCol(n)).End(xlUp).Row

.Range(.Cells(2, arrCol(n)), .Cells(LRow, arrCol(n))) _

.Replace what:="-", replacement:=""

.Cells(LRow + 1, arrCol(n)) = "End"

Start = 2

For i = Start To LRow

If .Cells(i + 1, arrCol(n)) > .Cells(i, arrCol(n)) Then

dblAvg = WorksheetFunction.AverageIf(.Range(.Cells(Start,

arrCol(n)), _

.Cells(i, arrCol(n))), ">0", .Range(.Cells(Start,

arrCol(n)), _

.Cells(i, arrCol(n))))

.Range(.Cells(Start, arrCol(n)), .Cells(i,

arrCol(n))).Replace _

what:=0, replacement:=dblAvg, lookat:=xlWhole

.Range(.Cells(Start, arrCol(n)), .Cells(i,

arrCol(n))).Replace _

what:="", replacement:=dblAvg, lookat:=xlWhole

Start = i + 1

End If

Next

.Range(.Cells(2, arrCol(n)), .Cells(LRow, arrCol(n))).NumberFormat

= "0"

Next

End With

End Sub

Regards

Claus B.