Summing an unkown number of columns

  • Thread starter Thread starter lesley1000
  • Start date Start date
L

lesley1000

I put into Excel weekly figures (there change each week and time I put them
in). I want to do a sum of the row at the end to generate a total. Doing this
in excel is fine, but I want the code to sum from column D (this is fixed
each week) until the last column, which will be different each time I run the
macro. I've tried naming the cells, but VBA doesn't do it correctly, it will
do a fixed number of columns. Any suggestions?
 
I am running Excel 2003

Why not just sum from column D to column IV (i.e. D4:IV4). Any empty
cells will be treated a zero.
 
I put into Excel weekly figures (there change each week and time I put them
in). I want to do a sum of the row at the end to generate a total. Doing
this
in excel is fine, but I want the code to sum from column D (this is fixed
each week) until the last column, which will be different each time I run
the
macro. I've tried naming the cells, but VBA doesn't do it correctly, it
will
do a fixed number of columns. Any suggestions?
Click to expand...

You mentioned running a macro, so I assume you need the described
functionality for use in a macro that is doing more than just summing up
these columns (otherwise it would be more efficient to get the sum at the
spreadsheet level than from VBA code). If that is the case, you can use this
function to sum the columns from Column D onward. Any blank cells, or cells
containing text that cannot be converted to a numeric value, will be skipped
over. Simply pass in the Row number you want to perform the summation for.

Function SumColumns(RowNumber As Long) As Double
Dim X As Long
Dim LastColumnInRow As Long
LastColumnInRow = ActiveSheet.Cells(RowNumber, Columns.Count). _
End(xlToLeft).Column
For X = 4 To LastColumnInRow
If IsNumeric(Cells(RowNumber, X)) Then
SumColumns = SumColumns + Cells(RowNumber, X).Value
End If
Next
End Function

Rick
 
Cannot do that because it'll be a circular formula - as the formula will be
at the end of the row of cells I want to sum
 
Back
Top