Shift to end of column

[Guest]

Can someone help with a relatively simple task. I have columns of data with specific headings. I have created names for each of the colums and now need to write a macro that will:

1. go to a required column
2. do a calculation (say average) of the data in that column and place the result 2 cells BELOW the last filled cell in that column

Can someone smart out there help me with the code? Many thanks

 

[Nick Hodge]

Hume

In a roundabout sort of way the code below will do it. (Use it as a starter)

Sub AverageColumn()
Dim lColumnLetter As String
Dim sTopOfRange As String, sBottomofRange As String
Dim rRange As Range, dAverage As Double
lColumnLetter = InputBox("Enter a column letter", "Average Column")
sTopOfRange = Range(lColumnLetter & "1").Address
sBottomofRange = lColumnLetter & Range(lColumnLetter &
"65536").End(xlUp).Row
Set rRange = Range(sTopOfRange & ":" & sBottomofRange)
dAverage = Application.WorksheetFunction.Average(rRange)
rRange.End(xlDown).Offset(2, 0).Value = dAverage
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)