Hey all

I have written this code to counta() a range of cells, however it is very ugly can someone please provide suggestions on how this can be better achieved?

Basically the problem is to code a dynamic range into a formula.

Public Sub count_trans()

' Determine what the last row with data is 
lastRow = ActiveSheet.UsedRange.Rows.Count

' Determine what the first row with data is 
    Set rng = Cells.Find(What:="As of Date", After:=ActiveCell, lookin:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
    
    If Not rng Is Nothing Then
        rng.Activate
        ActiveCell.Rows("1:1").Select
        ActiveCell.Offset(1, 0).Select
        firstRow = ActiveCell.Row
    End If

ActiveCell.End(xlDown).Offset(2, 0).Select

'insert formula at 2 cells down of the last populated cell in column B
ActiveCell.Value = "=counta(B" & (lastRow - 2) & ":B" & (firstRow) & ")"

End Sub
Thank you