MyAdventureHat
New Member
- Joined
- Sep 17, 2014
- Messages
- 4
Hello,
I have a list of expenditures that gets longer as time goes on. I used the following code to select the last cell with data in column J, skip two rows down, and add a subtotals formula. Now I want to drag the formula to the right, but can’t seem to set it up right. The starting row will always be different but the starting column will be “J”. I tried using the variable “LastColumn” to autofill the formula but need a starting point for the range. Thank you for your help!
Private Sub AddSubtotals()
Dim BottomCell As Range
Dim strSubtotalFormula As String
Dim LastColumn As Long
With ActiveSheet
LastColumn = Range("1" & Columns.Count).End(xlToLeft).Column
End With
Set BottomCell = Range("J2").End(xlDown)
BottomCell.Select
ActiveCell.Offset(2, 0).Select
strSubtotalFormula = _
"=subtotal(9,J2:" & BottomCell.Address(False, False) & ")"
ActiveCell.Formula = strSubtotalFormula
Selection.AutoFill Destination:=Range("Don’t know what to put here" & LastColumn)
I have a list of expenditures that gets longer as time goes on. I used the following code to select the last cell with data in column J, skip two rows down, and add a subtotals formula. Now I want to drag the formula to the right, but can’t seem to set it up right. The starting row will always be different but the starting column will be “J”. I tried using the variable “LastColumn” to autofill the formula but need a starting point for the range. Thank you for your help!
Private Sub AddSubtotals()
Dim BottomCell As Range
Dim strSubtotalFormula As String
Dim LastColumn As Long
With ActiveSheet
LastColumn = Range("1" & Columns.Count).End(xlToLeft).Column
End With
Set BottomCell = Range("J2").End(xlDown)
BottomCell.Select
ActiveCell.Offset(2, 0).Select
strSubtotalFormula = _
"=subtotal(9,J2:" & BottomCell.Address(False, False) & ")"
ActiveCell.Formula = strSubtotalFormula
Selection.AutoFill Destination:=Range("Don’t know what to put here" & LastColumn)
