Summing coumns of differing lengths (XL2002)

Topic

New Reply

Hi. I am trying to put together a macro to import a csv file and turn it into a spreadsheet. Most has gone well, but I am stumped (again)!
The file is imported, formatted, etc, but I can’t seem to put together a formula to go to the bottom of a column and sum it (and the next, and next etc.)
The column lengths will be different each time I run the report that creates the csv file, so the code has to figure that out.

Thanks

Reply | Quote

Replies

The following macro is not meant as a definitive solution, but as a starting point. You will have to adapt it to your situation.

Sub SetColumnSums()
Dim lngCols As Long
Dim lngRow As Long
Dim lngCol As Long
lngCols = Range(“IV1”).End(xlToLeft).Column
For lngCol = 1 To lngCols
lngRow = Cells(65536, lngCol).End(xlUp).Row
If lngRow < 65536 Then
Cells(lngRow + 1, lngCol).FormulaR1C1 = _
"=Sum(R1C" & lngCol & ":R" & lngRow & "C" & lngCol & ")"
End If
Next lngCol
End Sub

If you have certain columns that shouldn't be summed, modify the line For lngCol = 1 To lngCols accordingly. For instance, if the first column contains labels, change it to For lngCol = 2 To lngCols.

If you have certain rows that shouldn't be summed, modify the formula that is being generated. For example, if the first two rows contain labels, change "=Sum(R1C" to "=Sum(R3C".

Note: the underscore _ in the line ending with FormulaR1C1 = _ is a continuation character; it indicates that the instruction is continued on the next line. The underscore must be preceded by a space.

Reply | Quote

That was a thing of beauty!! Thank you so much.
All that concatenating of the parts of the formula was a little unexpected.
Worked like a charm and I will get a lot of mileage out of this.

Thanks, again,

Dan

Reply | Quote