PDA

View Full Version : Insert End



adamsm
02-24-2011, 06:38 AM
Hi,

I'm trying to figure out a code that would insert the text "End" below the last data row of column "D" when the worksheet is activated.

Any help on this would be kindly appreciated.

Thanks in advance.

mdmackillop
02-24-2011, 06:40 AM
Private Sub Worksheet_Activate()
Cells(Rows.Count, 4).End(xlUp)(2) = "End"
End Sub

adamsm
02-24-2011, 06:51 AM
Thanks for the code mdmackilop. I've changed the code slight bit.

My intention is to add the formula to the column "T" so that each time the worksheet gets activated the formula gets copied to the row below the last data row and sum ups the column from row 17.

But my modification isn't working. How may I do what is being told above?

Private Sub Worksheet_Activate()
Cells(Rows.Count, 4).End(xlUp)(2) = "End"
Cells(Rows.Count, 20).End(xlUp)(2) = "=SUBTOTAL(109,T1:T10000)"
End Sub

Any help on this would be kindly appreciated.

mdmackillop
02-24-2011, 12:27 PM
I'm not clear on your sub-total function. What is the 109 value? I'd have thought you want something like
Cells(Rows.Count, 20).End(xlUp)(2).FormulaR1C1 = "=SUBTOTAL(9,R1C:R[-1]C)"

adamsm
02-26-2011, 12:38 AM
Thanks for the help. It Worked Fine. And I do really appreciate your help.