Last Column Macro

[B Smith]

Ok, I've written a macro out to calculate the totals of columns from D
to the end of that sheet. I need the sum formula to fill to the right
from D + lastrow to the last column's last row.

Here's my code. (just ignore the msgbox thing- I'll take that out when
it works)


Sub totals()

Dim endcell
Dim lastrow
Dim lastcolumn
Set endcell = ActiveSheet.UsedRange
lastrow = endcell(endcell.Count).Row
lastcolumn = endcell(endcell.Count).column
Range("D" + CStr(lastrow + 1)).Select
ActiveCell.FormulaR1C1 = "=SUM(R2C:R" + CStr(lastrow) + "C)"
MsgBox CStr(lastcolumn)
Selection.AutoFill Destination:=Range("D" + CStr(lastrow + 1) + ":"
+ CStr(lastcolumn) + CStr(lastrow + 1)), Type:=xlFillDefault

End Sub


After hours of trying to debug, I realized that my count for lastcolumn
returns a number- I need a letter (my number right now is 49). I think
I've gotten everything else right, but any other things wrong with it
would be greatly appreciated. Anyways, how can I change my macro to
make it work? Thanks for your time.

Brett Smith

 

[Jim Cone]

Brett,

The Cells property appears to be what you need...
Cells(lastrow, lastcolumn) specifies a single cell.

So the AutoFill code line could be rewritten as...
Selection.AutoFill Destination:=Range(Cells(lastrow + 1, 4), _
Cells(lastrow + 1, lastcolumn)), Type:=xlFillDefault

It is also best to use the "&" sign when joining text instead the plus sign.
The "+" sign should be reserved for addition.


Regards,
Jim Cone
San Francisco, USA

 

[Bernie Deitrick]

Brett,

Here's how I would do it:

Sub TotalsV2()
Dim EndCell As Range

Set EndCell = ActiveSheet.UsedRange
EndCell.Offset(EndCell.Rows.Count, 0).Resize(1).FormulaR1C1 = _
"=SUM(R2C:R" + CStr(EndCell.Item(EndCell.Cells.Count).Row) + "C)"
End Sub

But here is how to correct your code: you need to use the Cells method
(which takes numeric row and column) to return a range object.

Sub TotalsCorrected()

Dim endcell As Range
Dim lastrow As Long
Dim lastcolumn As Integer

Set endcell = ActiveSheet.UsedRange

lastrow = endcell(endcell.Count).Row
lastcolumn = endcell(endcell.Count).Column

Range("D" & lastrow + 1).FormulaR1C1 = _
"=SUM(R2C:R" + CStr(lastrow) + "C)"

Range("D" & lastrow + 1).AutoFill Destination:=Range("D" & CStr(lastrow +
1), _
Cells(lastrow + 1, lastcolumn)), Type:=xlFillDefault

End Sub

HTH,
Bernie

 

[B Smith]

Thanks for both of your help- with a little tweaking to Bernie's v2
totals macro and Jim's & tip I got my macro working with some
formatting added to it as well. Thanks again for all of your help!

Here's my code:



Sub totals() 'for security reports
Dim EndCell As Range
Dim lastrow

Set EndCell = ActiveSheet.UsedRange
lastrow = EndCell(EndCell.Count).Row

'to fill with totals
EndCell.Offset(EndCell.Rows.Count, 0).Resize(1).FormulaR1C1 = _
"=SUM(R2C:R" + CStr(EndCell.Item(EndCell.Cells.Count).Row) + "C)"

'formatting
Rows(CStr(lastrow + 1) & ":" & CStr(lastrow + 1)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
With Selection.Interior 'gray background
.ColorIndex = 15
.Pattern = xlSolid
End With

'deleting unneeded subtotals
Range("A" & CStr(lastrow + 1) & ":C" & CStr(lastrow + 1)).Select
Selection.ClearContents
End Sub