VBA to put counta subtotal at end of column

[nirvehex]

Hi,

How would I put this function into a VBA code that looks at column A and puts this function in the cell after the very last cell with any data in it. Assume there are no blanks within the data in column A.

So I want to find the last row of data in column A and run this formula in the next row down. Furthermore how do I make the ending cell (a14) so it automatically runs the formula to the last cell with data in it.

=SUBTOTAL(103,'1 yr of PO''s'!$A$2:$A$14)

Thanks.

 

[bbott]

Try this UNTESTED code:

Sub SubTtl()

    Dim x As Long
    
    x = Range("A" & Range("A" & Rows.Count).End(xlUp).Row)
    Range("A" & x).Offset(2, 0).Formula = _
        "=SUBTOTAL(103,'1 yr of PO''s'!$A$2:$A$" & x & ")"
        
End Sub

 

[Bazligton]

Hi,

This is just the kind of code I have been looking for but the subtotal CountA needs to appear at the end of column F, starting at F2 on sheet1.

Can you help.


Many Thanks.